mysql主从同步 ===================== MASTER(主数据库操作) ===================== ubunt linux: 1.修改mysql配置文件 my.cnf: [mysqld] #修改主数据server-id和启用bin-log server-id = 1 log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M 2.查看log-bin是否启用成功: ON为开启状态 mysql> show variables like 'log%'; +---------------------------------+--------------------------+ | Variable_name | Value | +---------------------------------+--------------------------+ | log | OFF | | log_bin | ON | | log_bin_trust_function_creators | OFF | | log_error | /var/log/mysql/error.log | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_queries | OFF | | log_warnings | 1 | +---------------------------------+--------------------------+ 3.授权Slave同步用户 mysql> grant replication slave on *.* to 'admin'@'%' identified by 'admin'; 4.查看Master状态(日志文件和日志位置) mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000200 | 1933734 | | | +------------------+----------+--------------+------------------+ ===================== SLAVE(从数据库操作) ===================== 1.修改Master数据库IP,用户名,密码,日志文件,文件当前位置 mysql> change master to master_host='192.168.4.127',master_user='admin',master_password='admin', master_log_file='mysql-bin.000200',master_log_pos=1890742; 2.导出主数据库的文件,然后倒入从数据库 3.启动主从同步 mysql> start slave; 4.查看slave状态 mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: xxx.xx.xx.xx Master_User: xxxxx Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000007 Read_Master_Log_Pos: 628 Relay_Log_File: AFODY-604201049-relay-bin.000003 Relay_Log_Pos: 593 Relay_Master_Log_File: binlog.000007 Slave_IO_Running: Yes Slave_SQL_Running: Yes 注意:Slave_IO_Running,Slave_SQL_Running: 都为YES时才是正确的 主从同步最好使用相同的数据库版本,我测试了一下,使用 master 版本为 5.5.* slave 版本为 5.7.* 会出现 Slave_IO_Running: NO 因为 5.6有增加了一个 server-uuid 属性 旧版本没有这个属性,所以出问题了,这个问题折腾我半个小时.... ===================== 测试 ===================== 1.进入主数据库 mysql> create database dog; Query OK, 1 row affected (0.08 sec) 2.进入从数据库 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | dog | | jcticket | | mysql | | performance_schema | | sys | | test | | ticket | +--------------------+ 8 rows in set (0.00 sec) ==================== SUCCESS ====================
环境与配置
系统:CentOS6.5
Master:172.16.19.2
Slave:172.16.19.24
mysql-proxy:172.16.19.14
安装就不说了,基本都是yum install进行安装的。下图是基本的架构图(忘了从哪里扒拉出来的了,感谢作者)。
主从复制的实现
主从复制的实现极其简单,只需要改一下vim /etc/my.cnf。
Master的配置:
vim /etc/my.cnf log-bin=mysql-bin #新增 server-id=1 #新增
配置完之后,重启mysql,然后执行如下指令:
master:mysql> grant replication slave on *.* to 'root'@'%' identified by '123456'; 然后执行show master status Mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000005 | 261 | | | +------------------+----------+--------------+------------------+
用脑袋或者纸笔记录一下File以及Positin,在这里是mysql-bin.000005以及261。
Slave的配置:
1 vim /etc/my.cnf 2 log-bin=mysql-bin #新增 3 server-id=2 #新增 server-id不能一样
同样的,配置完成之后需要重启mysql服务,然后执行如下指令:
mysql> change master to master_host='172.16.19.2',master_user='root',
master_password='123456',master_log_file='mysql-bin.000005',master_log_pos=261;
mysql> start slave;
接下来,查看slave的状态,查看是否配置成功:
1 show slave status\G 2 ============================================== 3 **************** 1. row ******************* 4 Slave_IO_State: 5 Master_Host: 172.16.19.2 6 Master_User: rep1 7 Master_Port: 3306 8 Connect_Retry: 60 9 Master_Log_File: mysql-bin.000005 10 Read_Master_Log_Pos: 261 11 Relay_Log_File: localhost-relay-bin.000008 12 Relay_Log_Pos: 561 13 Relay_Master_Log_File: mysql-bin.000005 14 Slave_IO_Running: YES 15 Slave_SQL_Running: YES 16 Replicate_Do_DB: 17 ……………省略若干…………… 18 Master_Server_Id: 1 19 1 row in set (0.01 sec) 20 ==============================================
其中Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES,才表明状态正常。
主从复制的测试
先查看主服务器Master:
然后创建一个数据库,y:
接着去从服务器slave查看,会发现从服务器也有了y数据库。至此数据库的主从复制就实现了。有点晚了,至于说读写分离就下次再说吧