主从复制2——拥有海量数据主服务器的主从复制模型详细实现；
基本策略：
创新互联专业为企业提供天桥网站建设、天桥做网站、天桥网站设计、天桥网站制作等企业网站建设、网页设计与制作、天桥企业网站模板建站服务，十多年天桥做网站经验，不只是建网站，更提供有价值的思路和整体网络服务。
此时需要在主服务器上先完全备份，还原到从服务器；接着开启主从复制； 如果直接使用主从复制，那么主从服务器的压力很大；
主服务器数据全备份操作：
[root@master ~]$MySQLdump -A -F --single-transaction --master-data=1 > all.sql
主服务器模拟修改操作：
MariaDB [(none)]> create database wangdb1; Query OK, 1 row affected (0.01 sec) MariaDB [(none)]> show master logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 8217 | | mariadb-bin.000002 | 555 | | mariadb-bin.000003 | 334 | +--------------------+-----------+ 3 rows in set (0.00 sec) [root@master ~]$less all.sql CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=245;
主服务器配置：
[root@slave ~]$vim /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. innodb_file_per_table server_id=2
从服务器数据还原：
[root@master ~]$scp all.sql 192.168.27.17:~ [root@slave ~]$mysql < all.sql
从服务器开启主从复制：
MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.27.7', -> MASTER_USER='repluser', -> MASTER_PASSWORD='centos', -> MASTER_LOG_FILE='mariadb-bin.000003', -> MASTER_LOG_POS=245; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.27.7 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000003 Read_Master_Log_Pos: 334 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 620 Relay_Master_Log_File: mariadb-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 334 Relay_Log_Space: 916 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set (0.00 sec)
从服务器数据一致性保证：
由于从服务器上的数据也是可以被删除的，所以为了保证数据的一致性，可以将只读属性打开； 这种情况下，MySQL的root用户依然是可以修改删除的，只是对于普通用户生效； MariaDB [(none)]> show variables like 'read_only'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | OFF | +---------------+-------+ 1 row in set (0.00 sec) [root@slave ~]$vim /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. innodb_file_per_table server_id=2 read_only MariaDB [(none)]> grant select,update,delete on *.* to test@'192.168.27.%' identified by 'centos'; Query OK, 0 rows affected (0.00 sec) [root@master ~]$mysql -utest -pcentos -h292.168.27.17 MariaDB [hellodb]> select * from students where age='22'; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 21 | Huang Yueying | 22 | F | 6 | NULL | +-------+---------------+-----+--------+---------+-----------+ 3 rows in set (0.00 sec) MariaDB [hellodb]> delete from students; ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement
分享文章：主从复制2——拥有海量数据主服务器的主从复制模型详细实现；
网页URL：http://azwzsj.com/article/jhssge.html
