mysql主从同步配置

关于如何安装mysql
Linux
Windows
Docker

主

修改my.cnf文件并重启

# vim /etc/my.cnf
## 添加如下
server-id =1 	      		# 保证唯一
log-bin=mysql-bin   		# 让主机生成二进制SQL日志执行文件
# service mysql restart		# 重启mysql服务器

重启后登录数据库查询是否生效

> SHOW VARIABLES LIKE 'server_id'

value字段是my.cnf设置的server-id值一样即生效

添加连接用户并设置远程登录权限

# mysql -u root -p
> GRANT REPLICATION SLAVE ON *.* TO 'test'@'%' IDENTIFIED BY '123456';
> ALTER USER 'test'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

刷新所有的表，同时给数据库加上一把锁，阻止对数据库进行任何的写操作

> flush tables with read lock;

获取File 与Position

> show master status; 

从节点设置同步后对数据库解锁

> unlock tables;

从

修改my.cnf文件并重启（同主节点设置一样 id一定要不同）

# vim /etc/my.cnf
## 添加如下
server-id =2 	      		# 保证唯一
log-bin=mysql-bin   		# 让主机生成二进制SQL日志执行文件
service mysql restart		# 重启mysql服务器

重启后登录数据库查询是否生效

> SHOW VARIABLES LIKE 'server_id'

value字段是my.cnf设置的server-id值一样即生效

同步设置并开启

设置同步，根据主服务器的ip地址，生成的权限账号的用户名和密码以及二进制文件的名称还有文件大小进行同步

MASTER_HOST			主节点地址
MASTER_PORT 		端口号(可以不加，默认3306)
MASTER_USER			用户
MASTER_PASSWORD		密码
MASTER_LOG_FILE		主节点获取的File
MASTER_LOG_POS 		主节点获取的Position

> CHANGE MASTER TO MASTER_HOST='192.168.1.111',MASTER_PORT=3306,MASTER_USER='test',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=111;
> START SLAVE		# 开启同步 

测试一下即可，在主节点数据库修改个数据，从节点同步了即可，注意从节点变更数据库，主节点不会同步

问题

主节点和从节点数据库结构不一致

主节点备份数据

备份数据到文件mysql.bak.sql

# mysqldump --all-databases -uroot -p -hlocalhost > mysql.bak.sql

从节点数据恢复

> stop slave 	# 停止同步
> source /home/test/mysql.bak.sql		# 恢复数据
> start slave	# 开启同步

查看同步状态

> show slave status

看Slave_IO_Running与Slave_SQL_Running字段是否都为YES

windows修改my.ini无效

原因是mysql没有指定从my.ini配置文件启动
注册表编辑器依次打开：HKEY_LOCAL_MACHINE–>SYSTEM–>CurrentControlSet–>Services–>MySQL
修改ImagePath

"C:\Program Files\mysql\bin\mysqld "  MySQL
改成
"C:\Program Files\mysql\bin\mysqld "  --defaults-file="C:\Program Files\mysql\bin\my.ini" MySQL

重新启动mysql即可

添加log-bin=mysql-bin重启不起来

把名字改一下，可能是重复导致的起不来，如改成log-bin=mysql

Master command COM_REGISTER_SLAVE failed: Access denied for user ‘test’@’%’ (using password: YES) (Errno: 1045)

开启同步后，查看同步状态show slave status，Slave_IO_Running 一直处在Connecting 状态，没有与主节点同步，看到Last_IO_Errno 字段为Master command COM_REGISTER_SLAVE failed: Access denied for user ‘test’@’%’ (using password: YES) (Errno: 1045)

> show slave status; 
|Slave_IO_Running |Last_IO_Errno |
|Connecting |Master command COM_REGISTER_SLAVE failed: Access denied for user 'test'@'%' (using password: YES) (Errno: 1045)|

发现是同步账户连接不了数据库，权限问题，到主节点数据库执行如下

> GRANT REPLICATION SLAVE ON *.* TO  'test'@'%' identified by '123456';