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
在备份是一定要--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
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.进行两端自增长测试
3306mysql> 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)
3307mysql> 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',文件位置多了空格。切记不可加空格!!!!