简单配置MySQL主从同步实例

MySQL主从同步
Master，记录数据更改操作
启用binlog日志；设置binlog日志格式；设置server_id
Slave，运行两个线程
Slave_IO：复制master主机binlog日志文件里的SQL到本机的relay-log文件里
Slave_SQL：执行本机relay-log文件里的SQL语句，重现Master的数据操作
确保数据相同（从库必须要有主库的数据，可用备份的方法）—>主服务器（启用binlog日志、授权用户、查看当前正使用的日志）—>从服务器（设置server_id，指定主库信息）—>测试（客户端连接主库写入数据，在从库上也能查询到）

步骤一：配置主服务器（51）

# vim /etc/my.cnf 
[mysqld] 
log_bin=master51 
server_id=51 
binlog_format="mixed" 
# systemctl restart mysqld 
# mysql -uroot -p123456 
mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq..A";  
mysql> show master status;                //要记住日志文件和偏移位置 
mysql> system mysqlbinlog /var/lib/mysql/master51.000001 | grep -i grant GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' IDENTIFIED WITH 'mysql_native_password' AS       '*19DE595CEDBD7B65D0F6E430826E2D100F1831C9' 
（grant也会写入到binlog日志中） 

步骤二：配置从服务器（52）

# mysql -h192.168.4.51 -urepluser -p123qqq..A 
mysql> show grants; 
mysql> quit 
# vim /etc/my.cnf 
[mysqld] 
server_id=52                       //不能与其他主机id相同 
# systemctl restart mysqld 
# mysql -uroot -p123456 
mysql> change master to 
    -> master_host="192.168.4.51", 
    -> master_user="repluser", 
    -> master_password="123qqq..A", 
    -> master_log_file="master51.000001", 
-> master_log_pos=441; 
mysql> show slave status\G; 
mysql> start slave; 
mysql> show slave status\G; 
排错可以看Last_IO_Error、Last_SQL_Error字段  

步骤三：测试主从同步效果（50）
51:

# mysql -uroot -p123456 
mysql> create database db1; 
mysql> create table db1.a(id int); 
mysql> grant select,insert on db1.* to yaya100@"%" identified by "123qqq..A"; 

50:

# mysql -h192.168.4.51 -uyaya100 -p'123qqq..A' 
mysql> show grants; 
mysql> insert into db1.a values(100),(101),(200); 
mysql> select * from db1.a; 

52:

# mysql -uroot -p123456 
mysql> select * from db1.a; 

如果把从服务器(52)的库或者表删掉，客户端(50)往主服务器(51)写入数据，从服务器的SQL线程会直接宕掉

master.info：主库信息
relay-log.info：中继日志文件
主机名-relay-bin.xxxxxx：中继日志
主机名-relay-bin.index：索引文件
重启从服务器的数据库服务，存在这四种文件，所以依然会同步

从服务器(52)还原成独立数据库服务器：

# cd /var/lib/mysql 
# rm -rf mysql52-relay-bin.* 
# rm -rf master.info  
# rm -rf relay-log.info  
# systemctl restart mysqld 
# mysql -uroot -p123456 -e "show slave status\G"     //-e：在命令行输SQL命令 

重新设置回从服务器：

mysql> change master to 
    -> master_host="192.168.4.51", 
    -> master_user="repluser", 
    -> master_password="123qqq..A", 
    -> master_log_file="master51.000001", 
-> master_log_pos=1852;           
   //偏移量可以根据需求，取当前主服务器使用的binlog日志中1852之前的，但是必须存在于当前使用的binlog日志文件中 
mysql> start slave; 
mysql> show slave status\G; 

设置临时不同步： stop slave—>客户端写入数据—>从服务器不同步

主从同步常用配置参数
1.写在master数据库服务器配置文件里的参数（对所有slave服务器生效）
binlog_do_db=name---------------设置master对哪些库记日志
binlog_ignore_db=name----------设置master对哪些库不记日志

2.写在slave数据库服务器配置文件里的参数（只对从服务器自己生效）
log_slave_updates----------------记录从库更新，允许链式复制（A-B-C）
relay_log=xxxxx-relay-bin-----指定中继日志文件名
replicate_do_db=name---------仅复制指定库，其他库将被忽略，此选项可设置多条
replicate_ignore_db=name----不复制哪些库，其他库将被忽略（与do_db二选一）

根据需求，在配置文件/etc/my.cnf添加

[mysqld]
选项=值

例：
主服务器(51)

允许同步的库 
# vim /etc/my.cnf 
[mysqld] 
binlog_do_db=db1,db2 
# systemctl restart mysqld 
# mysql -uroot -p123456 
mysql> show master status; 

不允许同步的库

# vim /etc/my.cnf 
[mysqld] 
#binlog_do_db=db1,db2 
binlog_ignore_db=db9 
# systemctl restart mysqld 
# mysql -uroot -p123456 
mysql> show master status; 

从服务器(52)
允许同步的库

# vim /etc/my.cnf 
[mysqld] 
replicate_do_db=db2 
# systemctl restart mysqld 
# mysql -uroot -p123456 
mysql> show slave status\G; 

不允许同步的库

# vim /etc/my.cnf 
[mysqld] 
#replicate_do_db=db2 
replicate_ignore_db=studb 
# systemctl restart mysqld 
# mysql -uroot -p123456 
mysql> show slave status\G; 

主从同步模式
1.结构模式
主从同步结构：
单向复制：主—>从
链式复制：主—>从—>从
互为主从：主<—>主（两台数据库服务器不能同时被访问，一般应用在高可用集群）
一主多从：从<—主--->从

一主多从

51：

# mysqldump -uroot -p123456 -A > /root/db1.sql 
# scp /root/db1.sql 192.168.4.53:/root     
mysql> show master status; 

53：

# mysql -uroot -p123456 
mysql> create database db1; 
mysql> use db1; 
mysql> source /root/db1.sql 
mysql> change master to 
    -> master_host="192.168.4.51", 
    -> master_user="repluser", 
    -> master_password="123qqq..A", 
    -> master_log_file="master51.000003", 
    -> master_log_pos=154; 
mysql> start slave; 
mysql> show slave status\G; 

50：

# mysql -h192.168.4.51 -uyaya100 -p'123qqq..A' 
mysql> insert into db1.a values(777); 
mysql> insert into db1.a values(777); 
mysql> insert into db1.a values(777); 
mysql> select * from db1.a; 

52：

mysql> select * from db1.a; 

53：

mysql> select * from db1.a; 

主从从
要在既做主库又做从库的服务器中，添加log_slave_updates，记录从库更新，允许链式复制（A-B-C）

53(先还原成独立数据库服务器)：

# cd /var/lib/mysql 
# rm -rf master.info  
# rm -rf mysql53-relay-bin.* 
# rm -rf relay-log.info  
# systemctl restart mysqld 
# mysql -uroot -p123456 -e "show slave status\G" |grep -i yes 

52(要做51的从库，也要开binlog日志，做用户授权)：

# vim /etc/my.cnf 
[mysqld] 
log-bin=master52 
log_slave_updates 
binlog_format="mixed" 
server_id=52 
# systemctl restart mysqld 
# mysql -uroot -p123456 
mysql> grant replication slave on *.* to repluser@"%"  identified by "123qqq..A"; 
mysql> show master status; 
mysql> show slave status\G; 

53(配置成52的从服务器)：

# mysql -uroot -p123456 
mysql> change master to 
-> master_host="192.168.4.52", 
-> master_user="repluser", 
    -> master_password="123qqq..A", 
    -> master_log_file="master52.000001", 
    -> master_log_pos=441; 

50(测试)：

# mysql -h192.168.4.51 -uyaya100 -p'123qqq..A' 
mysql> insert into db1.a values(777); 
mysql> insert into db1.a values(777); 
mysql> insert into db1.a values(777); 
mysql>  select * from db1.a; 

52：

mysql>  select * from db1.a; 

53：

mysql>  select * from db1.a; 

复制模式
异步复制
主库执行完一次事务后，立即将结果返给客户端，并不关心从库是否已经接收并处理

全同步复制
当主库执行完一次事务后，且所有从库都执行了该事务后才返回给客户端

半同步复制
主库在执行完一次事务后，等待至少一个从库接收到并写到relay log中才返回给客户端

查看数据库服务是否允许动态加载模块：

mysql> show variables like "have_dynamic_loading"; 

查看是否安装了模块：

mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like "%semi%"; 

安装模块（如果两台都有主从关系两个模块都安装）：
51：

mysql> install plugin rpl_semi_sync_master 
    -> soname "semisync_master.so"; 
mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like "%semi%"; 

52：

mysql> install plugin rpl_semi_sync_slave 
    -> soname "semisync_slave.so"; 
mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like "%semi%"; 

启用半同步复制（如果两台都有主从关系两个都启用）
51：

mysql> set global rpl_semi_sync_master_enabled = 1; 
mysql> show variables like "rpl_semi_sync_%_enabled"; 

52：

mysql> set global rpl_semi_sync_slave_enabled = 1; 
mysql> show variables like "rpl_semi_sync_%_enabled"; 

永久启用半同步复制：

# vim /etc/my.cnf 
[mysqld]下添加 
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" 
rpl_semi_sync_master_enabled = 1 
rpl_semi_sync_slave_enabled = 1 
# systemctl restart mysqld