12月14日任务
17.1 MySQL主从介绍
17.2 准备工作
17.3 配置主
17.4 配置从
17.5 测试主从同步
MySQL主从介绍
MySQL主从又叫做Replication、AB复制。简单将就是A/B两个服务器做主从后，在A上写数据，B也会跟着写数据，两者数据是实时同步的。
MySQL主从是基于binlog的，主服务器需要开启binlog才能进行主从配置。
主从配置大致有3个步骤：
- 主服务器将更改操作记录到binlog里；
- 从服务器将主服务器的binlog事件（sql语句）同步到本机并极力到relaylog里；
- 从服务器根据relaylog里面的sql语句按顺序执行；
主服务器上有一个log dump线程，用来和从服务器的I/O线程传递binlog。
从服务器上有两个线程，其中I/O线程用来同步主服务器的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的SQL语句落地。
应用场景：
- 数据备份，在主服务器出现故障时，从服务器代替主服务器提供读取服务；
- 备份的同时web服务器会到从服务器上读取数据，减轻主服务器的数据读取压力；
MySQL安装
在测试主从的2台服务器上都安装上MySQL，具体操作步骤如下：
# 2台服务器配置相同，这里只写出其中一台
[root@master src]# wget http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz
[root@master src]# tar zxf mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz
[root@master src]# mv mysql-5.6.36-linux-glibc2.5-x86_64 /usr/local/mysql
[root@master src]# cd /usr/local/mysql/
[root@master mysql]# useradd mysql
[root@master mysql]# mkdir /data
[root@master mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql
[root@master mysql]# cp support-files/my-default.cnf /etc/my.cnf
cp：是否覆盖"/etc/my.cnf"？ y
# 修改配置文件内basedir和datadir参数
[root@master mysql]# vi /etc/my.cnf
修改[mysqld]内的2行即可
basedir = /usr/local/mysql
datadir = /data/mysql
保存退出
[root@master mysql]# cp support-files/mysql.server /etc/init.d/mysqld
# 修改mysqld配置文件，
[root@master mysql]# vi /etc/init.d/mysqld
同样要修改一下参数
basedir=/usr/local/mysql
datadir=/data/mysql
[root@master mysql]# chmod 755 /etc/init.d/mysqld
[root@master mysql]# chkconfig --add mysqld
- 启动MySQL
[root@master mysql]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/localhost.localdomain.err'.
.. SUCCESS!
[root@master mysql]# ps aux | grep mysqld
root 2856 0.0 0.1 113264 1616 pts/0 S 13:52 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/localhost.localdomain.pid
mysql 2990 8.5 45.0 1308984 450220 pts/0 Sl 13:52 0:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/localhost.localdomain.err --pid-file=/data/mysql/localhost.localdomain.pid
root 3019 0.0 0.0 112676 972 pts/0 R+ 13:53 0:00 grep --color=auto mysqld
配置主服务器：192.168.65.133
log_bin参数只在主服务器上设置
修改my.cnf，增加server_id=133，log_bin=test1，修改后重启MySQL
会在/data/mysql目录下生成以test1前缀的多个文件
[root@master ~]# mysqldump -uroot -p1 test > /tmp/test.sql
[root@master ~]# mysql -uroot -p1 -e "create database test1"
[root@master ~]# mysql -uroot -p1 test1 < /tmp/test.sql
[root@master ~]# ls -l /data/mysql/test1.*
-rw-rw----. 1 mysql mysql 425 1月 15 19:39 /data/mysql/test1.000001
-rw-rw----. 1 mysql mysql 15 1月 15 19:36 /data/mysql/test1.index
创建用作同步数据的用户
mysql> grant replication slave on *.* to 'repl'@'192.168.65.134' identified by 'test2';
Query OK, 0 rows affected (0.00 sec)
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.05 sec)
mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| test1.000001 | 636 | | | |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
可选操作：
将/data/mysql下的除mysql外的库都进行备份(如果有其他库的话)
例如：mysqldump -uroot -p1 zrlog > /tmp/zrlog.sql
配置从服务器：192.168.65.134
修改my.cnf，增加server_id=134。重启服务
[root@backup ~]# vi /usr/local/mysql/my.cnf
[root@backup ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
同步备份的数据库
[root@backup ~]# scp 192.168.65.133:/tmp/*.sql /tmp
The authenticity of host '192.168.65.133 (192.168.65.133)' can't be established.
ECDSA key fingerprint is 42:50:a7:09:91:db:af:77:a5:3a:b3:67:1c:8a:5b:99.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.65.133' (ECDSA) to the list of known hosts.
[email protected]'s password:
test.sql 100% 1258 1.2KB/s 00:00
创建主服务器同名用户
[root@backup ~]# mysql -uroot -p1
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
恢复数据
# 同步过来的有多少个数据库就做多少个恢复
[root@backup ~]# mysql -uroot -p1 test < /tmp/test.sql
登录mysql，执行从配置
[root@backup ~]# mysql -uroot -p1
//master_log_file填主服务器show master status;显示的file内容
//master_log_pos填主服务器show master status;显示的position内容
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.65.133', maste_user='repl', master_password='test2', master_log_file='test1.000001', master_log_pos=636;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
查看是否连接成功
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.65.133
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: test1.000001
Read_Master_Log_Pos: 636
Relay_Log_File: server-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: test1.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
下列2行都为Yes即成功连接
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
完成后到主服务器上执行解锁命令
mysql> unlock tables;
测试主从同步
常用参数分析
主服务器上
binlog-do-db= //仅同步主服务器上指定的库，多个库用逗号分隔
binlog-ignore-db= //忽略指定库
从服务器上
指定同步从服务器上的库、表
replicate_do_db=
replicate_ignore_db=
replicate_do_table=
replicate_ignore_table=
replicate_wild_do_table= //支持通配符%，例如test.%（库.表）
replicate_wild_ignore_table=
测试
- 创建操作
# 主服务器上新建一个db库
mysql> create database db;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
#从上也创建了一个数据库db
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
- 删除操作
主服务器上
# 删除表
mysql> drop table wp_user;
从服务器上
# 从上的表也被删了
mysql> select * from wp_user;
ERROR 1146 (42S02): Table 'mysql.wp' doesn't exist
在数据一致的前提下重连主从：
主上：
mysql> show master status\G
从上：
mysql> stop slave;
mysql> change master to master_host='192.168.65.133', master_user='repl', master_password='test2', master_log_file='', master_log_pos=新id;
mysql> start slave;
数据不一致： 需要重新配置主从：先将主服务器上的数据库重新备份，再在从服务器上重新配置。