1.主服务开启log_bin
# vim /etc/mysql/my.cnf
....
server-id = 55
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
...
修改完mysql配置后重启mysql
# service mysql restart
授权从机权限
mysql > grant replication slave on *.* to slave@120.78.62.47 identified by "123456";
刷新权限
mysql> flush privileges;
查看主状态
mysql> show master status
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 2821 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
重启mysql#
#service mysql restart
2.从机
开启备份
# vim /etc/mysql/my.cnf
server-id = 33
log_bin = /var/log/mysql/mysql-bin.log
重启mysql
# service mysql restart
mysql> change master to
master_host='47.52.100.111',
master_user='slave',
master_log_file='mysql-bin.000012',
master_password='123456',
master_log_pos=106028;
如果报错
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log
请删除文件:
/var/lib/mysql/master.info
/var/lib/mysql/relay-log.info
开启主从备份并查看
mysql > start slave
mysql > show slave status\G;
....
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
表示成功
复制数据库
由于备份数据并没有初始起来,原因也查了很多,没有解决,想到了个折中的办法:复制数据库,
mysqldump bshop-test -uroot -p123456 --add-drop-table | mysql bshop -uroot -p23456
bshop-test为被复制对象,然后再复制回来,达到数据初始数据一致
mysqldump bshop -uroot -p123456 --add-drop-table | mysql bshop-test -uroot -p123456