MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后,在A上写数据,另外一台B也会跟着写数据,两者数据实时同步的。也就是说,当你在A机器写入一个表,再次查看B机器也会同步一个表。
1.1 MySQL主从是基于binlog的,主上须开启binlog才能进行主从。
主从过程大致有3个步骤:
1.2 主从原理图:
Master:主mysql
Slave:备mysql
当有数据写进主mysql中的时候,就出自动生成一个log dump thread,记录到如上讲的binlog中,然后从数据库就会和主mysql有个线程进行交互,从服务器就会读取binlog日志到Slave,然后再次生成一个relay log(中继日志),然后再次和从服务器中的SQL thread线程进程交互执行。
来段大白话:
从服务器把主服务器上的binlog弄到自己服务器上去,然后根据这个binlog生成自己的relay日志,根据这个relay日志进行更改数据库,最终达到两边数据一致。
1.3 主从的作用;
假如A服务器(主)突然硬件问题,宕机了。但是线上跑了很多重要的数据,我们完全可以使用B服务器(从)直接顶上。
我们线上的所有数据均是从A上面读取,由于压力比较大,我们可以使用B服务器分享一部分用户到自己的服务器,但是只是可以读,不可以写入数据。看图可知,数据的读取是有方向性的,如果首先从B上读取那就混乱了,数据肯定不一致了。就会导致主从失败!
2.1 配置my.cnf
在之前配置LAMP和LNMP的时候只是配置了datadir和socket,
现在我们需要增加server-id=144和log_bin=zhdy1(server-id为了混淆我使用的IP地址的最后一位,log_bin需要自定义一个名字,此为bin_log的前缀)
修改完配置文件后,启动或者重启mysqld服务。
[root@zhdy-03 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
2.2查看生成的新文件:
[[email protected]03 ~]# cd /data/mysql
[[email protected]03 mysql]# ll
total 176296
-rw-rw----. 1 mysql mysql 56 Aug 25 00:23 auto.cnf
drwx------. 2 mysql mysql 80 Aug 25 00:18 db1
drwx------. 2 mysql mysql 4096 Aug 25 00:18 db123
-rw-r-----. 1 mysql mysql 79691776 Aug 30 19:20 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Aug 30 19:20 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Aug 25 00:18 ib_logfile1
drwx------. 2 mysql mysql 4096 Aug 25 00:18 lalala
drwx------. 2 mysql mysql 4096 Aug 25 00:18 mysql
drwx------. 2 mysql mysql 4096 Aug 25 00:18 mysql2
drwx------. 2 mysql mysql 4096 Aug 25 00:18 performance_schema
drwx------. 2 mysql mysql 20 Aug 25 00:18 test
-rw-r-----. 1 mysql mysql 499 Aug 25 00:18 xtrabackup_info
drwx------. 2 mysql mysql 4096 Aug 25 00:18 zhdy01
drwx------. 2 mysql mysql 36864 Aug 25 00:18 zhdy02
drwx------. 2 mysql mysql 12288 Aug 25 00:18 zhdy03
-rw-rw----. 1 mysql mysql 55367 Aug 30 19:20 zhdy-03.err
-rw-rw----. 1 mysql mysql 5 Aug 30 19:20 zhdy-03.pid
-rw-rw----. 1 mysql mysql 120 Aug 30 19:20 zhdy1.000001
-rw-rw----. 1 mysql mysql 15 Aug 30 19:20 zhdy1.index
zhdy1.000001;zhdy1.index 这两个文件非常重要,是我们实现主从的根本。
其实在上面我们还可以看到很多的之前做测试使用的数据库,我们今天就用这些数据库做测试:
2.3 备份数据库
[[email protected]03 mysql]# mysqldump -uroot -pzhangduanya zhdy01 > /tmp/zhdy01.sql
Warning: Using a password on the command line interface can be insecure.
2.4 创建一个新的库并把之前的数据恢复
[[email protected]03 mysql]# mysql -uroot -pzhangduanya -e "create database haha"
Warning: Using a password on the command line interface can be insecure.
[[email protected]03 mysql]# mysql -uroot -pzhangduanya haha < /tmp/zhdy01.sql
Warning: Using a password on the command line interface can be insecure.
2.5 创建用作同步数据的用户
mysql> grant replication slave on *.* to 'repl'@'192.168.240.142' identified by 'zhangduanya';
授予repl从服务器ip为192.168.240.142, 密码为zhangduanya允许权限为replication slave
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 |
+--------------+----------+--------------+------------------+-------------------+
| zhdy1.000001 | 403309 | | | |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
我们需要记住file 和Position。待会会用到。
2.6 查看当前数据库,开始备份
[[email protected]03 mysql]# ls
auto.cnf db123 ibdata1 ib_logfile1 mysql performance_schema xtrabackup_info zhdy02 zhdy-03.err zhdy1.000001
db1 haha ib_logfile0 lalala mysql2 test zhdy01 zhdy03 zhdy-03.pid zhdy1.index
再此,我用到的数据库为:haha,zhdy01,zhdy02,zhdy03,然后我就模拟线上的环境,把如上经常用到的全部备份并开始同步。
2.7 备份数据库
zhdy01数据库我已经备份了(haha也是用的zhdy01的数据),再次备份其它两个:
[[email protected]03 mysql]# mysqldump -uroot -pzhangduanya zhdy02 > /tmp/zhdy02.sql
Warning: Using a password on the command line interface can be insecure.
[[email protected]03 mysql]# mysqldump -uroot -pzhangduanya zhdy03 > /tmp/zhdy03.sql
Warning: Using a password on the command line interface can be insecure.
[[email protected]03 mysql]# ls /tmp/*.sql
/tmp/zhdy01.sql /tmp/zhdy02.sql /tmp/zhdy03.sql
3.1 配置my.cnf
配置server-id=132,要求和主不一样。bin_log就不需要配置了,二进制文件只需要在主服务器上面配置即可。
server-id=132
3.2 重新启动Mysql
[root@zhdy-02 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
3.3 把主服务器上面的数据库全部copy过来
[[email protected]02 ~]# scp 192.168.230.144:/tmp/*.sql /tmp/
[email protected]'s password:
zhdy01.sql 100% 393KB 392.9KB/s 00:00
zhdy02.sql 100% 2245KB 2.2MB/s 00:00
zhdy03.sql 100% 307KB 306.7KB/s 00:00
3.4 创建和主服务器上面一样的数据库:
mysql> create database haha;
Query OK, 1 row affected (0.02 sec)
mysql> create database zhdy01;
Query OK, 1 row affected (0.00 sec)
mysql> create database zhdy02;
Query OK, 1 row affected (0.00 sec)
mysql> create database zhdy03;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| haha |
| mysql |
| performance_schema |
| test |
| zhdy01 |
| zhdy02 |
| zhdy03 |
+--------------------+
8 rows in set (0.00 sec)
3.5 导入数据:
[root@zhdy-02 ~]# mysql -uroot haha < /tmp/zhdy01.sql
[root@zhdy-02 ~]# mysql -uroot zhdy01 < /tmp/zhdy01.sql
[root@zhdy-02 ~]# mysql -uroot zhdy02 < /tmp/zhdy02.sql
[root@zhdy-02 ~]# mysql -uroot zhdy03 < /tmp/zhdy03.sql
[root@zhdy-02 ~]# ls /data/mysql/
haha ib_logfile1 zhdy01 zhdy02 zhdy03
3.6 配置从服务器
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
如下就是实现主从非常关键的一步:
mysql> change master to master_host='192.168.230.144', master_user='repl', master_password='zhangduanya', master_log_file='zhdy1.000001', master_log_pos=403309;
Query OK, 0 rows affected, 2 warnings (0.10 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
master_host=’192.168.230.144’//主mysql的IP;
master_user=’repl’//刚刚在主服务器上创建的用户名;
master_password=’zhangduanya’//密码
master_log_file=’zhdy1.000001’//此为刚刚主服务器上面show master status看到的其中一个file的值;
master_log_pos=403309//如上
3.7 配置全部成功了,如何测试配置的是否成功?
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.230.144
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: zhdy1.000001
Read_Master_Log_Pos: 404262
Relay_Log_File: zhdy-02-relay-bin.000002
Relay_Log_Pos: 1232
Relay_Master_Log_File: zhdy1.000001
Slave_IO_Running: 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: 404262
Relay_Log_Space: 1407
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: 144
Master_UUID: 98d511ff-88e8-11e7-94f3-000c29896c09
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)
show slave stauts\G
看是否有
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
还需关注
Seconds_Behind_Master: 0 //为主从延迟的时间
当然我在配置好了之后也出现了错误:
Slave_IO_Running: connecting
Slave_SQL_Running: Yes
仔细检查后发现主mysql配置授权的时候密码有问题。重新授权后再次查看状态就ok了!
3.8 到主服务器(zhdy-03)执行恢复写操作:
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
4.1 主服务器上
binlog-do-db= //仅同步指定的库
binlog-ignore-db= //忽略指定库
假如在主服务器上面有很多数据库,但是我只想同步zhdy01这个库,需要在:
vim /etc/my.cnf
binlog-do-db=zhdy01
要是多个的话就用英文状态下的逗号去分隔;
或者有时候数据库比较多,我就一个zhdy02库不需要同步:
vim /etc/my.cnf
binlog-ignore-db=zhdy02
4.2 从服务器上
replicate_do_db=
replicate_ignore_db=
replicate_do_table=
replicate_ignore_table=
有时候我们需要在同步的时候忽略一些临时的表,这些表堵我们的用处不大,都是临时的。不建议使用上面去完成,有风险且会导致其它表有时候也不正常。所以建议用下面的两个参数去定义:
replicate_wild_do_table= //如zhdy01.%, 支持通配符%
replicate_wild_ignore_table=
4.3 测试主从
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db123 |
| haha |
| lalala |
| mysql |
| mysql2 |
| performance_schema |
| test |
| zhdy01 |
| zhdy02 |
| zhdy03 |
+--------------------+
12 rows in set (0.00 sec)
mysql> use haha;
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_haha |
+-----------------------+
| wp_commentmeta |
| wp_comments |
| wp_links |
| wp_options |
| wp_postmeta |
| wp_posts |
| wp_term_relationships |
| wp_term_taxonomy |
| wp_termmeta |
| wp_terms |
| wp_usermeta |
| wp_users |
+-----------------------+
12 rows in set (0.00 sec)
mysql> select count(*) wp_users;
+----------+
| wp_users |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
我们看到了wp_users有一条数据存在,
从服务器操作:(查看wp_users的数据)
mysql> select count(*) wp_users;
+----------+
| wp_users |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
我尝试把主server上面的wp_users表内的内容清空:
mysql> truncate table wp_users;
Query OK, 0 rows affected (0.05 sec)
再次去从server去查看:
mysql> select * from wp_users;
Empty set (0.00 sec)
数据已经清空了!!!
再次测试删除一个表:
(!!!这绝对是危险动作,我的haha数据库是测试的,所以尽管删除好了,线上的机器一定要三思!!!)
主server操作:
mysql> drop table wp_users;
Query OK, 0 rows affected (0.06 sec)
从server查看:
mysql> show tables;
+-----------------------+
| Tables_in_haha |
+-----------------------+
| wp_commentmeta |
| wp_comments |
| wp_links |
| wp_options |
| wp_postmeta |
| wp_posts |
| wp_term_relationships |
| wp_term_taxonomy |
| wp_termmeta |
| wp_terms |
| wp_usermeta |
+-----------------------+
11 rows in set (0.01 sec)
wp_users表已经没有了!
千万别在从server上面删除任何数据,一旦删除也就意味着数据不是一致的,主从就失败了!
如何再次恢复呢?
mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| zhdy1.000001 | 404480 | | | |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
记住Postion的这个值!
mysql> stop slave;
mysql> change master to master_host='192.168.230.144', master_user='repl', master_password='zhangduanya', master_log_file='zhdy1.000001', master_log_pos=404480;
mysql> start slave;
mysql> show slave status\G
再次去验证就发现已经再次连接了!