MySQL主从介绍、准备工作、配置主、配置从、测试主从同步
2018-12-15 本文已影响0人
XiaoMing丶
目录
一、MySQL主从介绍
二、准备工作
三、配置主
四、配置从
五、测试主从同步
一、MySQL主从介绍
-
MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步。
-
MySQL主从是基于binlog的，主上须开启binlog才能进行主从。binlog是一个二进制文件，无法使用cat等命令查看文件，在文件中记录了一些日志。
-
主从过程大致有3个步骤：
1）主将更改操作记录到binlog里。
2）从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里。
3）从根据relaylog里面的sql语句按顺序执行。 -
主上有一个log dump线程，用来和从的I/O线程传递binlog。
-
从上有两个线程，其中I/O线程用来同步主的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的sql语句落地。
-
主从使用场景：
1）数据备份：从机器实时同步主机器的数据，一旦主机器宕机，从机器可以马上上线继续提供服务。
2）减轻主的压力：客户端读操作时，服务器可以将部分请求发给从，减轻主的压力。写操作则只能在主上执行，避免数据混乱。
二、准备工作
- 在主从机器上均安装好mysql并正常运行。
三、配置主
- 修改主的配置文件my.cnf
[root@minglinux-01 ~] vim /etc/my.cnf
//修改my.cnf，增加server_id=130和log_bin=minglinux1（我这里原来已经配置有，仅需修改）
···
log_bin = minglinux1 //打开binlog，指定binlog的前缀
server_id = 130 //定义Mysql服务的ID号
···
- 重启mysql
[root@minglinux-01 ~] /etc/init.d/mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL...... SUCCESS!
[root@minglinux-01 ~] ls -lt /data/mysql/
总用量 112884
-rwxr-xr-x 1 mysql mysql 50331648 12月 15 14:05 ib_logfile0
-rwxr-xr-x 1 mysql mysql 12582912 12月 15 14:05 ibdata1
-rwxr-xr-x 1 mysql mysql 82501 12月 15 14:05 minglinux-01.err
-rw-rw---- 1 mysql mysql 5 12月 15 14:05 minglinux-01.pid
-rw-rw---- 1 mysql mysql 120 12月 15 14:05 minglinux1.000001 //二进制binlog文件，可能生成多个
-rw-rw---- 1 mysql mysql 20 12月 15 14:05 minglinux1.index //索引文件
-rw-rw---- 1 mysql mysql 9853 12月 15 14:05 minglinux-01.000013
drwx------ 2 mysql mysql 324 12月 13 19:21 zrlog
-rw-rw---- 1 mysql mysql 286 12月 12 14:55 minglinux-01.index
-rw-rw---- 1 mysql mysql 143 12月 12 14:55 minglinux-01.000012
-rw-rw---- 1 mysql mysql 666606 12月 8 23:02 minglinux-01.000011
drwx------ 2 mysql mysql 4096 12月 6 23:25 mysql2
-rw-rw---- 1 mysql mysql 592 12月 6 20:42 minglinux-01.000010
-rw-rw---- 1 mysql mysql 443 12月 5 22:40 minglinux-01.000009
-rw-rw---- 1 mysql mysql 143 12月 5 22:21 minglinux-01.000008
-rwxr-xr-x 1 mysql mysql 427 12月 5 22:09 minglinux-01.000007
-rwxr-xr-x 1 mysql mysql 120 12月 1 21:37 minglinux-01.000006
-rwxr-xr-x 1 mysql mysql 143 11月 25 22:19 minglinux-01.000005
-rwxr-xr-x 1 mysql mysql 170 11月 25 22:19 minglinux-01.000004
-rwxr-xr-x 1 mysql mysql 143 11月 23 23:12 minglinux-01.000003
-rwxr-xr-x 1 mysql mysql 56 11月 23 23:09 auto.cnf
-rwxr-xr-x 1 mysql mysql 1396530 11月 23 22:55 minglinux-01.000002
-rwxr-xr-x 1 mysql mysql 69432 11月 23 22:55 minglinux-01.000001
drwxr-xr-x 2 mysql mysql 4096 11月 23 22:55 mysql
drwxr-xr-x 2 mysql mysql 4096 11月 23 22:55 performance_schema
-rwxr-xr-x 1 mysql mysql 50331648 11月 23 22:55 ib_logfile1
drwxr-xr-x 2 mysql mysql 6 11月 23 22:55 test
//minglinux1前缀的文件对实现主从非常重要，没有这些文件主从无法完成
- 备份zrlog库作为测试数据
[root@minglinux-01 ~] mysqldump -uroot -p123456 zrlog > /tmp/zrlog.sql
Warning: Using a password on the command line interface can be insecure.
[root@minglinux-01 ~] du -sh /tmp/zrlog.sql
12K /tmp/zrlog.sql
- 创建新的库并将zrlog的备份恢复到新库
[root@minglinux-01 ~] mysql -uroot -p123456 -e "create database ming"
Warning: Using a password on the command line interface can be insecure.
[root@minglinux-01 ~] mysql -uroot -p123456 ming < /tmp/zrlog.sql
Warning: Using a password on the command line interface can be insecure.
[root@minglinux-01 ~] ls -l /data/mysql
//binlog文件变大，因为该文件会完整记录数据库创建过程。
//用户可以通过binlog文件恢复表中的数据。
···
-rw-rw---- 1 mysql mysql 10250 12月 15 14:18 minglinux1.000001
-rw-rw---- 1 mysql mysql 20 12月 15 14:05 minglinux1.index
···
- 创建用作同步数据的用户
mysql> grant replication slave on *.* to 'repl'@'192.168.162.132' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
//为保证主从数据一致，此时最好将主锁表，拒绝所有写操作
mysql> flush tables with read lock; //锁表（不允许再向表写数据）
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| minglinux1.000001 | 10461 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
//记住binlog文件的filename和position
- 将数据库中其他一些库也备份到/tmp中
[root@minglinux-01 ~] mysqldump -uroot -p123456 ming > /tmp/ming.sql
Warning: Using a password on the command line interface can be insecure.
[root@minglinux-01 ~] mysqldump -uroot -p123456 mysql2 > /tmp/mysql2.sql
Warning: Using a password on the command line interface can be insecure.
四、配置从
- 修改slave的配置文件my.cnf
[root@minglinux-02 ~] vim /etc/my.cnf
//设置server_id =132，要和master不一样的数字
···
server_id = 132
···
- 重启mysqld服务
[root@minglinux-02 ~] /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@minglinux-02 ~] ls /data/mysql/
auto.cnf minglinux-02.000001 minglinux-02.err mysql
ibdata1 minglinux-02.000002 minglinux-02.index performance_schema
ib_logfile0 minglinux-02.000003 minglinux-02.pid test
ib_logfile1 minglinux-02.000004 minglinux2.000001
jfedu minglinux-02.000005 minglinux2.index
- 将master上的库备份文件拷贝到slave上
远程复制(使用scp或者rsync)
[root@minglinux-02 ~] scp 192.168.162.130:/tmp/*.sql /tmp/
root@192.168.162.130's password:
ming.sql 100% 9868 3.6MB/s 00:00
mysql2.sql 100% 644KB 18.6MB/s 00:00
zrlog.sql 100% 9869 3.2MB/s 00:00
- 创建对应的三个数据库
mysql> create database ming;
Query OK, 1 row affected (0.00 sec)
mysql> create database mysql2;
Query OK, 1 row affected (0.00 sec)
mysql> create database zrlog;
Query OK, 1 row affected (0.00 sec)
- 备份文件恢复到数据库
[root@minglinux-02 ~] mysql -uroot ming < /tmp/ming.sql
[root@minglinux-02 ~] mysql -uroot mysql2 < /tmp/mysql2.sql
[root@minglinux-02 ~] mysql -uroot zrlog < /tmp/zrlog.sql
//恢复后主从上的库要保持一致
- 在从的mysql上执行操作
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> change master to master_host='192.168.162.130', master_user='repl', master_password='123456', master_log_file='minglinux1.000001', master_log_pos=10461;
Query OK, 0 rows affected, 2 warnings (0.02 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: 192.168.162.130
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: minglinux1.000002 //binlog文件
Read_Master_Log_Pos: 332
Relay_Log_File: minglinux-02-relay-bin.000003 //relaylog文件（中继日志）
Relay_Log_Pos: 496
Relay_Master_Log_File: minglinux1.000002
Slave_IO_Running: Yes //两项参数都为yes说明主从同步配置成功
Slave_SQL_Running: Yes //
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 332
Relay_Log_Space: 840
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0 //主从复制延迟的时间
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 130
Master_UUID: ca35c6b0-ef31-11e8-b545-000c29460125
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
ERROR:
No query specified
- 在主服务器上解锁表
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
五、测试主从同步
- 介绍几个配置参数
主服务器上：
binlog-do-db= //仅同步指定的库
binlog-ignore-db= //忽略指定库，同步其余的库
从服务器上：
replicate_do_db= //仅同步指定的库
replicate_ignore_db= //忽略指定库，同步其余的库
replicate_do_table= //同步指定的表
replicate_ignore_table= //忽略某些表
//若用户忽略了mysql库，那么use mysql后的select、update、insert等操作都将被忽略不再被记录到relaylog中。比如use mysql后有一个联合查询使用语句select zrlog.*，该语句也会被忽略，这会导致数据同步不完整。
replicate_wild_do_table= //支持通配符 ，如ming.%表示ming库所有的表
replicate_wild_ignore_table= //支持通配符
- 测试
在主上查询：
mysql> use ming
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_ming |
+----------------+
| comment |
| link |
| log |
| lognav |
| plugin |
| tag |
| type |
| user |
| website |
+----------------+
9 rows in set (0.00 sec)
mysql> select count(*) tag;
+-----+
| tag |
+-----+
| 1 |
+-----+
1 row in set (0.00 sec)
在从上查询：
mysql> use ming;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_ming |
+----------------+
| comment |
| link |
| log |
| lognav |
| plugin |
| tag |
| type |
| user |
| website |
+----------------+
9 rows in set (0.00 sec)
mysql> select count(*) tag;
+-----+
| tag |
+-----+
| 1 |
+-----+
1 row in set (0.00 sec)
//主从数据一致，均为一行
在主上清空表：
mysql> select * from tag;
+-------+-------+--------+
| tagId | count | text |
+-------+-------+--------+
| 1 | 1 | 记录 |
+-------+-------+--------+
1 row in set (0.00 sec)
mysql> truncate table tag;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from tag;
Empty set (0.00 sec)
在从上查询：
mysql> select * from tag;
Empty set (0.00 sec)
在主上删除tag表：
mysql> drop table tag;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_ming |
+----------------+
| comment |
| link |
| log |
| lognav |
| plugin |
| type |
| user |
| website |
+----------------+
8 rows in set (0.00 sec)
在从上查询：
mysql> show tables;
+----------------+
| Tables_in_ming |
+----------------+
| comment |
| link |
| log |
| lognav |
| plugin |
| type |
| user |
| website |
+----------------+
8 rows in set (0.00 sec)
问题：如果在从上删了某个库，此时再到主上删除相同的库就会出现报错。因为在主上删除库时，从上会将该删除库的操作执行一遍，但此时从上已经没有对应的库了，所以会出现错误，主从断开。
解决方法：在从上执行stop slave再执行start slave看是否能解决问题，若未能解决则需要重做主从。
