mysql配置主从

一、部署mysql

1.1、先部署好两台mysql数据库，部署mysql可以查看我的文档

写文章-CSDN创作中心

二、开始配置mysql主从

2.1、主机配置

vim /etc/my.cnf 或者 vi /etc/my.cnf

#增加一下配置

log-bin=mysql-bin
server_id=101

#选择增加的参数

binlog-do-db= 数据库名 #需要同步的数据库

binlog-ignore-db = 数据库名 #不需要同步的数据库

重启数据库：systemctl resatrt mysql

2.2、主机上创建用于复制的数据库账号，可以使用已经创建好的，也可以新建，需要注意的是mysql8及之后版本，需要先创建好数据库账号

#mysql -uxxx -pxxx 进入数据库

#mysql8以下使用

grant replication slave on . to '数据库用户'@'%'identified by '123';

grant replication client on . to '数据库用户'@'%'identified by '123';

#mysql8及以上使用

grant replication client,replication slave on . to '数据库用户'@'%';

#刷新

flush privileges;

2.3、查看主库信息 show master status;

要记住file 和 position的值，也就是第一和第二列，后面用的上

2.4、配置从库

vim /etc/my.cnf 或者 vi /etc/my.cnf

#增加一下配置
server_id=202 #不能和主库的值一样

重启数据库：systemctl resatrt mysql

2.5、从库关联配置

#进入mysql数据库mysql -uxxx -pxxx

#停掉从的服务，这里一定要停掉服务再去执行下面的

stop slave; #8以下的可以试试slave stop

#配置关联master，3306可以默认不写，master_log_file和master_log_pos参考之前主库show masterstatus;查询出来的信息。

change master to master_host='192.168.3.206', master_port=3306,master_user='root', master_password='root',master_log_file='binlog.000004',master_log_pos=156;

#启动服务

start slave; #8以下的可以试试slave start

#查看是否成功

show slave status \G;

需要保证框起来的都是yes

2.6、show slave status \G;常见问题，本人部署中发现Slave_IO_Running: No,如果这里不正确，可以查看下他的报错

这里常见的有三种报错，一个是报server ids，一个是server UUIDs,还有一种是主库端口不通

#遇见UUIDs

找到data文件夹下的auto.cnf文件，修改里面的uuid值，保证各个主从的uuid不一样，重启数据库即可，可使用find / -name auto.cnf 查找

#遇见ids

找到/etc/my.cnf配置文件中的server_id，修改从库的server_id保证和复制结构中的其他db不一样，重启数据库即可，但是我这里试了依然不行，后面使用下面的方法

#查看server_id

show variables like 'server_id';

#停止slave后，手动修改server_id

mysql> stop slave;

mysql> set global server_id=2;#此处的数值和my.cnf里设置的一样就行

mysql> start slave;

再次查看show slave status \G;

这里需要注意的是我本地部署的时候，发现只要一重启数据库，server_id就会重置成1，然后和主库就一样了，所以这里不能重启数据库，如果知道这个是什么问题，可以留言给我补充。

三、如果主库已经存在数据库，创建好主从之后，如何把之前的数据库同步过来

3.1、使用mysqldump

#在从库使用mysqldump命令复制数据

[root@slave2 ~]# mysqldump --single-transaction --all-databases --master-data=1 --host=192.168.2.138 --user=root --password=root --apply-slave-statements | mysql -uroot -proot-hlocalhost

参数说明

–single-transaction参数可以对Innodb表执行非锁定导出。此选项将事务隔离模式设置为REPEATABLE READ，并在转储数据之前向服务器发送START TRANSACTION SQL语句。它仅适用于Innodb等事务表，因为它会在发出START TRANSACTION时转储数据库的一致状态，而不会阻塞任何应用程序。因此这里假定：1. 所有的应用数据表都使用Innodb引擎。2. 所有系统表数据在备份过程中不会发生变化。

–master-data参数会导致转储输出包含类似 CHANGE MASTER TO MASTER_LOG_FILE=‘binlog.000004’, MASTER_LOG_POS=1480; 的SQL语句，该语句指示主库的二进制日志坐标（文件名和位置）。如果选项值为2，则CHANGE MASTER TO语句将写为SQL注释，因此仅提供信息，不会执行。如果参数值为1，则该语句不会写为注释，并在重新加载转储文件时执行。如果未指定选项值，则默认值为1。

–apply-slave-statements参数会在CHANGE MASTER TO语句之前添加STOP SLAVE语句，并在输出结尾处添加START SLAVE语句，用来自动开启复制。

mysqldump方式的优点是可以进行部分复制，如在配置文件中定义replicate-do-table=db1.*，则用这种方法可以只复制db1库而忽略其它复制事件。缺点是由于mysqldump会生成主库转储数据的SQL语句，实际是一种逻辑备份方式所以速度较慢，不适用于大库。

3.2、如果可以脱机复制直接的数据，可以使用下面的方法

#1、停止复制的所有实例，在主从分别执行

ln -s /usr/local/mysql/bin/mysqladmin /usr/bin/mysqladmin

mysqladmin -hlocalhost -uroot -pwwwwww shutdown

#2、主库复制数据到从库

cd /data

scp -r mysql/* root@192.168.3.208:/usr/local/mysql/data/ #从库的数据文件路径

#3、在从库执行命令,删除auto.cnf文件

cd /data/mysql

rm -rf auto.cnf

#4、重启 实例，主从都需要执行

systemctl start mysql