mysql 主从复制步骤
1)下载MySQL(Ubuntu)
sudo apt-get install mysql-server mysql-client
2)更改mysql的配置文件
vi /etc/mysql/my.cnf
修改内容如下
#绑定本机ip地址,作为master
bind-address = 192.168.0.75
#作为master
server-id = 1
#去掉注释
log_bin = /var/log/mysql/mysql-bin.log
#需要复制的数据库
binlog_do_db = zm_gaiay_net_cn
保存更改
2)重启mysql
sudo service mysql restart
3)通过mysql client 进入命令行
mysql -u root -p
提示输入root密码
4)把复制权限给slave_user,并指定密码
grant replication slave on *.* to 'slave_user'@'%' identified by '123456';
5)连接binlog_do_db 对应的数据库 (zm_gaiay_net_cn)
use zm_gaiay_net_cn;
6)锁住表以免发生新的变化
flush tables with read lock;
7)查看master status
show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107 | zm_gaiy_net_cn | |
+------------------+----------+--------------+------------------+
从库中会使用到 file 和 position
从库中安装
1)安装mysql
2)更改mysql配置文件my.cnf
server-id = 2
#添加,注意这里是中划线
replay-log = /var/log/mysql/mysql-replay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = zm_gaiay_net_cn
3)重启服务
sudo service mysql restart
4)通过mysql-client 进入命令行界面
mysq -u root -p
配置主库
CHANGE MASTER TO MASTER_HOST='192.168.0.75',MASTER_USER='slave_user', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 107;
解释
master_host : 主库的ip
master_user :组库中用来复制的用户
master_password:主库中用来复制的用户密码
master_log_file:主库(zm_gaiay_net_cn)对应的bin文件
master_log_pos:从哪里开始复制
5)开启复制
start slave
6)查看状态
SHOW SLAVE STATUS