0-首先主从mysql最好一致,第一次使用主mysql和从mariadb,同步妥妥的起不来[?♂️]
1-配置server-id,在/etc/my.cnf (Ubuntu中在/etc/mysql/mysql.conf.d/mysqld.cnf) 中加入
log-bin=mysql-bin
server-id=222
数字不重复即可,重启mysqld: service mysqld restart
2-加入同步复制用户:
mysql -uroot -p’mima’ -e ‘grant replication slave on *.* to ‘mysync’@’%’ identified by ‘mima’;’
3-阻断mysql写入(一般可以停止访问mysql的服务)
4-使用mysqldump导出database
mysqldump -uroot -p –all-databases > master_bak.sql
5-查看master状态:
mysql -uroot -p’mima’ -e ‘show master status\G’
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 79637
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
6-恢复访问mysql的服务
7-登录从机,使用master_bak.sql恢复
mysql -uroot -p’mima’ < master_bak.sql
8-登录slave mysql
mysql> change master to master_host=’192.168.xxx.xxx’,master_user=’mysync’,master_password=’mima’,
master_log_file=’mysql-bin.000001′,master_log_pos=79637;
file和pos需要和导出时的master状态一致
9-打开slave:
mysql> start slave;
10-查看slave状态:Slave_IO_Running和Slave_SQL_Running是否为Yes
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.xxx.xxx
Master_User: mysync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 157946
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 99569
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
……
近期评论