mysql主从配置

mysql主从介绍

MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步。 
MySQL主从是基于binlog的，主上须开启binlog才能进行主从。

主从过程：

• 主将更改操作记录到binlog中
• 从将主的binlog事件（SQL语句）同步到本机并记录在relaylog中
• 从根据relaylog里面的SQL语句按顺序执行

主从原理图：

该过程有三个线程，主上有一个log dump线程，用来和从的i/o线程传递binlog；从上有两个线程，其中i/o线程用来同步主的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的SQL语句落地

主从的使用场景：

1. 备份重要数据
2. 分担主库数据读取压力

准备工作

准备两台linux，都装上MySQL

安装MySQL：http://blog.csdn.net/aoli_shuai/article/details/78745984

问题：

[root@shuai-02 src]# wget http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz 
-bash: wget: 未找到命令

解决：

用

[root@shuai-02 src]# yum install -y wget

问题2：

[root@shuai-02 mysql]# ./scripts/mysql_install_db –user=mysql –datadir=/data/mysql 
-bash: ./scripts/mysql_install_db: /usr/bin/perl: 坏的解释器: 没有那个文件或目录

解决办法：安装Perl和perl-devel

[root@shuai-02 mysql]# yum install -y perl perl-devel

[root@shuai-02 mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql
FATAL ERROR: please install the following Perl modules before executing ./scripts/mysql_install_db:
Data::Dumper

解决方法，安装autoconf库

[root@shuai-02 mysql]# yum install -y autoconf

[root@shuai-02 mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql
Installing MySQL system tables..../bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

解决方法：

[root@shuai-02 mysql]# yum -y install libaio-devel

配置主

修改配置文件

[root@shuai-01 ~]# vi /etc/my.cnf

增加如下配置

server-id=135
log_bin=shuailinux1

完成配置后，重启MySQL服务

[root@shuai-01 ~]# /etc/init.d/mysqld restart

查看MySQL的库文件

[root@shuai-01 ~]# ls -lt /data/mysql
总用量 110712
-rw-rw----. 1 mysql mysql 50331648 1月  29 16:07 ib_logfile0
-rw-rw----. 1 mysql mysql 12582912 1月  29 16:07 ibdata1
-rw-rw----. 1 mysql mysql    92933 1月  29 16:07 shuai-01.err
-rw-rw----. 1 mysql mysql        5 1月  29 16:07 shuai-01.pid
-rw-rw----. 1 mysql mysql       21 1月  29 16:07 shuailinux1.index
-rw-rw----. 1 mysql mysql      120 1月  29 16:07 shuailinux1.000001
drwx------. 2 mysql mysql     4096 1月  28 22:28 zrlog
drwx------. 2 mysql mysql     4096 1月  17 01:00 mysql
drwx------. 2 mysql mysql       48 1月  17 00:46 db1
-rw-rw----. 1 mysql mysql       56 1月   4 21:20 auto.cnf
drwx------. 2 mysql mysql     4096 1月   4 21:08 performance_schema
-rw-rw----. 1 mysql mysql 50331648 1月   4 21:08 ib_logfile1
drwx------. 2 mysql mysql        6 1月   4 21:08 test

新建一个实验数据库

备份一个数据库
[root@shuai-01 mysql]# mysqldump -uroot -p111111 zrlog > /tmp/zrlog.sql

新建一个数据库
[root@shuai-01 mysql]# mysql -uroot -p111111 -e "create database shuai"
Warning: Using a password on the command line interface can be insecure.
将备份的数据恢复到新建的数据库中
[root@shuai-01 mysql]# mysql -uroot -p111111 shuai < /tmp/zrlog.sql 
Warning: Using a password on the command line interface can be insecure.

创建一个用作同步数据的用户：

[root@shuai-01 mysql]# mysql -uroot -p111111
mysql> grant replication slave on *.* to 'repl'@192.168.176.134 identified by '111111';
Query OK, 0 rows affected (0.14 sec)

这里的IP为从机器的IP

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.10 sec)

锁定表，让其暂时不能写，保持现状用于同步

mysql> show master status; 
+——————–+———-+————–+——————+——————-+ 
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 
+——————–+———-+————–+——————+——————-+ 
| shuailinux1.000001 | 10597 | | | | 
+——————–+———-+————–+——————+——————-+ 
1 row in set (0.02 sec)

记住file 和position，同步时会用到。

退出数据库

备份数据库中所有的库

[root@shuai-01 mysql]# mysqldump -uroot -p111111 shuai > /tmp/shuai.sql
Warning: Using a password on the command line interface can be insecure.

配置从

编辑配置文件

[root@shuai-02 mysql]# vim /etc/my.cnf

server-id=134

注意：log_bin只在主上设置重点内容 
重启

[root@shuai-02 mysql]# /etc/init.d/mysqld restart
Shutting down MySQL..... SUCCESS! 
Starting MySQL............................................................... SUCCESS! 

配置完成后，在从机器上连接主机器，获取数据库

[root@shuai-02 mysql]# scp 192.168.176.135:/tmp/*.sql /tmp/
root@192.168.176.135's password: 
shuai.sql                               100% 9906     9.7KB/s   00:00    
zrlog.sql                               100% 9906     9.7KB/s   00:00 

创建库：

[root@shuai-02 mysql]# mysql -uroot
mysql> create database shuai;
Query OK, 1 row affected (0.16 sec)

mysql> create database zrlog;
Query OK, 1 row affected (0.06 sec)

恢复数据库：

[root@shuai-02 mysql]# mysql -uroot shuai < /tmp/shuai.sql
[root@shuai-02 mysql]# mysql -uroot zrlog < /tmp/zrlog.sql

这个过程，一定要先停止主数据库的服务，不让它写数据。以保证主从两边数据一致。

实现主从同步：

在从数据库上

[root@shuai-02 mysql]# mysql -uroot

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.176.135',master_user='repl',master_password='111111',master_log_file='shuailinux1.000001',master_log_pos=10597;
Query OK, 0 rows affected, 2 warnings (0.32 sec)

IP为主的IP；file、pos分别为主的filename和position

检查主从是否建立成功：

mysql> start slave;
Query OK, 0 rows affected (0.15 sec)

mysql> show slave status\G

 
这里的两个yes

主从完成后，去主数据库，解锁表。

[root@shuai-01 mysql]# mysql -uroot -p111111
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=   忽略指定的表
使用最多，好用，支持库.表
replicate_wild_do_table=  如aming.%，支持通配符    
replicate_wild_ignore_table=

主数据库：

mysql> use shuai
mysql> show tables;
mysql> select count(*) user;
+------+
| user |
+------+
|    1 |
+------+
1 row in set (0.04 sec)

删除后
mysql> drop tables website;
Query OK, 0 rows affected (0.23 sec)

从数据库：

mysql> use shuai;
mysql> select count(*) user;
+------+
| user |
+------+
|    1 |
+------+
1 row in set (0.06 sec)
删除表后：
mysql> show tables;
+-----------------+
| Tables_in_shuai |
+-----------------+
| comment         |
| link            |
| log             |
| lognav          |
| plugin          |
| tag             |
| type            |
| user            |
+-----------------+
8 rows in set (0.00 sec)

在主从数据库时，只有在主数据库上删除，修改，从数据库是可以同步的，在从数据库上删除修改，就会使主从不同步，主从就会断开，只能从新做主从（change master）。