Ubuntu如何搭建mysql的slave
master:192.168.1.111
slave:192.168.1.212
1. master环境配置:
(1)修改配置文件:vim /etc/mysql/my.cnf,
#设置服务器Id值
server-id=1
(2)数据库配置:
#创建backup用户,并使得其有192.168.1.212的数据库连接权限
mysql> GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO backup@192.168.60.212 IDENTIFIED BY '123';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT FILE ON *.* TO backup@192.168.1.212 identified by '123' ;
Query OK, 0 rows affected (0.00 sec)
mysql> flush tables with read lock; //不要退出这个终端,否则这个锁就不生效了;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
//查看master状态;
mysql> show master status;
(3)dump数据,并拷贝到slave机器;
mysqldump -h192.168.1.111 -uroot --master-data=1 --single-transaction -B usermanager >master.dmp
scp master.dmp 192.168.1.212:/home
2. slave环境配置
(1)修改配置文件:
#主库IP
master-host=192.168.1.111
#用户名
master-user=backup
#密码
master-password=123
#端口
master-port=3306
#服务器ID
server-id=2
#同步间隔时长s
master-connect-retry=60
(2)验证连接master
mysql -h192.168.1.111 -ubackup -p123;
show grants for backup@192.168.1.111;
(3)停止slave,并执行master.dmp文件中CHANGE命令:
#停止slave
stop slave;
#打开master.dmp文件,复制:CHANGE MASTER TO xxx文字 如:复制:CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=823384;
#登陆mysql,执行第5步复制的CHANGE MASTER TO xxx文字 如:执行CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=823384;
#启动slave,
start slave ;
#查看slave运行状态
show slave status;
这样,在master上sso数据库的任何变化都需要同步到slave:192.168.1.212上.