一、配置MySQL运行环境
master1:192.168.1.25 CENTOS7.0 MySQL5.6.20 slave1:192.168.1.26 CENTOS7.0 MySQL5.6.20
安装MySQL数据库
wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm yum update yum install mysql-community-server
配置开机启动
systemctl enable mysqld.service
配置防火墙,Centos7.0防火墙默认配置工具是firewalld,因此,可以通过firewall-cmd进行配置
firewall-cmd --zone=public --add-port=3306/tcp --permanent systemctl restart firewalld.service
查看防火墙配置是否生效
iptables -L |grep mysql ACCEPT tcp -- anywhere anywhere tcp dpt:mysql ctstate NEW
二、配置Master1 (192.168.1.25)
Centos7.0 RPM安装MySQL默认配置文件在 /etc/my.cnf,因此:
vim /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock server-id=25 #enable log_bin log_bin=mysql-bin replicate-wild-ignore-table=mysql.% # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Recommended in standard MySQL setup sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
重新启动MySQL服务
systemctl restart mysqld.service
接下来创建同步复制的用户及授权
mysql>grant replication slave,replication client on *.* to 'repl'@192.168.1.26 identified by '123456'; mysql>flush privileges; mysql> show master status; +--------------------+----------+-----------------+------------------+----------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +--------------------+----------+-----------------+------------------+----------------+ | mysql-bin.000009 | 319 | onethink | | | +--------------------+----------+-----------------+------------------+----------------+ 1 row in set (0.00 sec)
锁定数据库表,禁止写入数据
mysql> flush tables with read lock; Query OK, 0 rows affected (2.53 sec)
备份需要同步的MySQL数据库,导出成SQL脚本
mysqldump -p3306 -uroot -p onethink > /tmp/onethink.sql
解除MySQL数据库表锁定
mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)
三、配置Slave1 (192.168.1.26)
编辑my.cnf
加入下面代码
server-id=26 replicate-wild-ignore-table=mysql.%
重新启动MySQL服务
systemctl restart mysqld.service
登录MySQL数据库
stop slave; CHANGE MASTER TO MASTER_HOST = '192.168.1.25', MASTER_USER = 'repl', MASTER_PASSWORD = '123456', MASTER_LOG_FILE = 'mysql-bin.000009', MASTER_LOG_POS = 319; start slave;
查看slave状态,主要查看下面两个参数
show slave status\G; Slave_IO_Running: Yes Slave_SQL_Running: Yes
由于我的虚拟机是直接复制的,Slave_IO_Running:No,通过查看/var/log/mysqld.log,发现如下错误:
master and slave have equal MySQL server UUIDs
通过排查,server_id是正确的,可能是由于复制导致MySQL UUID重复,将MySQL服务卸载重新安装即可。
四、my.cnf配置参数说明
#启用从库日志,这样可以进行链式复制 log-slave-updates #从库是否只读,0表示可读写,1表示只读 read-only=1 #只复制某些表(可用匹配符) replicate-wild-do-table=dbname.tablename% #只复制某个库 replicate-do-db=dbname #不复制某个表 replicate-ignore-table=dbname.tablename #不复制某些表 replicate-wild-ignore-table=dbname.tablename% #不复制某数据库所有表,通过真实修改的表进行过滤,更为准确 replicate-wild-ignore-table=dbname.% #不复制某个库,使用use db来确定是否过滤 replicate-ignore-db=dbname #复制完的sql语句是否立即从中继日志中清除,1表示立即清除 relay-log-purge=1 #从服务器主机,用于show slave hosts生成从库清单 report-host=hostname