MySQL主从复制简介
MySQL的主从复制方案与scp/rsync等文件级别同步是类似的,都是数据的传输。只不过MySQL无需借助第三方工具,而是其自带的复制功能,MySQL的主从复制并不是数据库磁盘上的文件直接拷贝复制,而是通过逻辑的Binlog日志复制到同步的数据本地然后读取里面SQL语句应用到数据库的过程。
实战操作
双实例端口
[root@db02 3306]# netstat -luntp|grep 330 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 64040/mysqld tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 55735/mysqld
MySQL版本号
mysql> select version(); +-----------+ | version() | +-----------+ | 5.5.32 | +-----------+ 1 row in set (0.00 sec)
1.修改双主my.cnf配置文件
3306
[root@db02 3306]# grep -B 6 "stop" my.cnf #______m-m m1 start_________ auto_increment_increment = 2 auto_increment_offset = 1 log-slave-updates log-bin = /data/3306/mysql-bin expire_logs_days = 7 #______m-m m1 stop_________
3307
[root@db02 3306]# grep -B 6 "stop" ../3307/my.cnf #______m-m m1 start_________ auto_increment_increment = 2 auto_increment_offset = 2 log-slave-updates log-bin = /data/3307/mysql-bin expire_logs_days = 7 #______m-m m1 stop_________
2.重启服务
[root@db02 3306]# ./mysql stop Stoping MySQL... [root@db02 3306]# ./mysql start Starting MySQL... [root@db02 3306]# ../3307/mysql stop Stoping MySQL... [root@db02 3306]# ../3307/mysql start Starting MySQL... [root@db02 3306]# netstat -luntp|grep 330 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3691/mysqld tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 4464/mysqld
3.进入3306数据库创建rep用户进行授权
mysql> grant replication slave on *.* to rep@'172.16.1.%' identified by 'oldboy123'; Query OK, 0 rows affected (0.00 sec) mysql> show grants for rep@'172.16.1.%'\G *************************** 1. row *************************** Grants for rep@172.16.1.%: GRANT REPLICATION SLAVE ON *.* TO 'rep'@'172.16.1.%' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' 1 row in set (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
4.备份3306数据库,并导入3307数据量。
[root@db02 3306]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -A -B -x --events --master-data=2|gzip >/server/backup/rep_3306_$(date +%F).sql.gz [root@db02 3306]# ls -l /server/backup/rep_3306_$(date +%F).sql.gz -rw-r--r-- 1 root root 144934 12月 25 22:57 /server/backup/rep_3306_2015-12-25.sql.gz [root@db02 3306]# gzip -d /server/backup/rep_3306_2015-12-25.sql.gz [root@db02 3306]# ll /server/backup/rep_3306_2015-12-25.sql -rw-r--r-- 1 root root 531949 12月 25 22:57 /server/backup/rep_3306_2015-12-25.sql [root@db02 3306]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock </server/backup/rep_3306_2015-12-25.sql
在备份是一定要--master-data=2,不然在导入3307时会报错,每次备份完成后检查。
4.进入3307数据库进行与3306复制的修改
[root@db02 3306]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. ........ mysql> CHANGE MASTER TO -> MASTER_HOST='172.16.1.52', -> MASTER_PORT=3306, -> MASTER_USER='rep', -> MASTER_PASSWORD='oldboy123'; Query OK, 0 rows affected (0.03 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.1.52 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 107 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes ........
5.进行测试
3306创建
mysql> create database abc; Query OK, 1 row affected (0.00 sec)
3307查看
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | abc | | mysql | | oldboy | | oldboy_gbk | | performance_schema | | test | +--------------------+ 7 rows in set (0.00 sec)
6.备份3307数据库并导入3306数据库。
[root@db02 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -A -B -x --events --master-data=2|gzip >/server/backup/rep_3307_$(date +%F).sql.gz [root@db02 ~]# ls -l /server/backup/rep_3307_$(date +%F).sql.gz -rw-r--r-- 1 root root 144734 12月 25 23:31 /server/backup/rep_3307_2015-12-25.sql.gz [root@db02 ~]# gzip -d /server/backup/rep_3307_2015-12-25.sql.gz [root@db02 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock </server/backup/rep_3307_2015-12-25.sql mysql> grant replication slave on *.* to rep@'172.16.1.%'; Query OK, 0 rows affected (0.00 sec)
7.进入3306数据库进行与3307复制的修改
[root@db02 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock ....... mysql> CHANGE MASTER TO -> MASTER_HOST='172.16.1.52', -> MASTER_PORT=3307, -> MASTER_USER='rep', -> MASTER_PASSWORD='oldboy123'; Query OK, 0 rows affected (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.1.52 Master_User: rep Master_Port: 3307 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1064183 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 1061699 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes
8.进行两端自增长测试
3306
mysql> CREATE TABLE `zizeng` ( -> `REL_ID` bigint(12) NOT NULL auto_increment COMMENT '流水id', -> `ARTICLE_TITLE` varchar(255) NOT NULL COMMENT '文章标题', -> PRIMARY KEY (`REL_ID`) -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.02 sec) mysql> insert into zizeng(ARTICLE_TITLE) values('odlboy'); Query OK, 1 row affected (0.00 sec) mysql> insert into zizeng(ARTICLE_TITLE) values('oldgirl'); Query OK, 1 row affected (0.00 sec) mysql> select * from zizeng; +--------+---------------+ | REL_ID | ARTICLE_TITLE | +--------+---------------+ | 1 | odlboy | | 3 | oldgirl | +--------+---------------+ 2 rows in set (0.00 sec)
3307
mysql> select * from zizeng; +--------+---------------+ | REL_ID | ARTICLE_TITLE | +--------+---------------+ | 1 | odlboy | | 3 | oldgirl | +--------+---------------+ 2 rows in set (0.00 sec) mysql> insert into zizeng(ARTICLE_TITLE) values('abc'); Query OK, 1 row affected (0.00 sec) mysql> insert into zizeng(ARTICLE_TITLE) values('abcd'); Query OK, 1 row affected (0.00 sec) mysql> select * from zizeng; +--------+---------------+ | REL_ID | ARTICLE_TITLE | +--------+---------------+ | 1 | odlboy | | 3 | oldgirl | | 4 | abc | | 6 | abcd | +--------+---------------+ 4 rows in set (0.00 sec)
常见问题
1、授权问题
mysql5.5授权的时候:
GRANT REPLICATION CLIENT ON *.* TO 'rep'@'10.0.0.%' IDENTIFIED BY PASSWORD '123456'
在主从复制的时候状态会一直显示connecting,所以在授权时请选择:
GRANT REPLICATION SLAVE ON *.* TO 'rep'@'10.0.0.%' IDENTIFIED BY PASSWORD '123456'
2、change master配置
change master 里面的东西如果设置错了,修改master info不会生效的,请执行 reset slave all;进行清除所有slave数据。
3、change master空格
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
这个是配置
CHANGE MASTER TO MASTER_HOST='172.16.1.52', MASTER_PORT=3306, MASTER_USER='rep', MASTER_PASSWORD='oldboy123', MASTER_LOG_FILE=' mysql-bin.000010', MASTER_LOG_POS=194;
其中MASTER_LOG_FILE=' mysql-bin.000010',文件位置多了空格。切记不可加空格!!!!
转载于:https://blog.51cto.com/jifucha/1728448