最近工作不是很忙,把以前整理的 MySQL 数据库的主从配置过程记录一下,有不足之处,请各位多多纠正指教。
- #环境配置#master IP:192.168.46.137 slave IP:192.168.46.138 database:v1
1. 在两台机器,分别安装 mysql 数据库,分别添加远程连接权限
2. 配置主数据库
- #编辑mysql的配置文件,增加以下内容#vim etc/my.cnfserver-id=1binlog-do-db=v1relay-log=/var/lib/mysql/mysql-relay-binrelay-log-index=/var/lib/mysql/mysql-relay-bin.indexlog-error=/var/lib/mysql/mysql.errmaster-info-file=/var/lib/mysql/mysql-master.inforelay-log-info-file=/var/lib/mysql/mysql-relay-log.infolog-bin=/var/lib/mysql/mysql-bin编辑完成后,重启mysql,systemctl restart mysqld.service(CentOS7 直接使用systemctl命令)创建一个复制用户,具有replication slave 权限grant replication slave on *.* to 'user3'@'192.168.46.138' identified by 'user3';用户名:user3 密码:user3flush privileges;#查看主库状态#show master status;
- 记住FileSet和Position参数值,后面步骤会用到
3. 配置从数据库
红色箭头所指两个参数, 如果都为 Yes, 说明配置成功, 如果 Slave_IO_Running 为 connecting, 请检查防火墙,端口是否开放,FIle,Position 参数是否一致,网络是否畅通等
- #编辑从数据库配置文件,添加以下内容#vim etc / my.cnfserver - id = 2replicate - do - db = v1relay - log = /var/lib / mysql / mysql - relay - binrelay - log - index = /var/lib / mysql / mysql - relay - bin.indexlog - error = /var/lib / mysql / mysql.errmaster - info - file = /var/lib / mysql / mysql - master.inforelay - log - info - file = /var/lib / mysql / mysql - relay - log.infolog - bin = /var/lib / mysql / mysql - bin注意: 两个数据库配置文件里的server - id不能相同重启mysqlmysql - u root - pxxxchange master to master_host = '192.168.46.137',
- master_user = 'user3',
- master_password = 'user3',
- master_log_file = 'mysql-bin.000003',
- master_log_pos = 120;#启动slave线程#start slave;# 查看slave状态,
- 注意G后面没有分号#show slave status\G
- 到此,mysql主从配置完成,可以新增数据查看是否同步了
来源: http://www.linuxidc.com/Linux/2017-04/143211.htm