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语句落地

主从的使用场景：

备份重要数据

分担主库数据读取压力

准备工作

准备两台linux，都装上MySQL

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

问题：

[root@ma-2 src]# wget http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz 

-bash: wget: 未找到命令

解决：

用

[root@ma-2 src]# yum install -y wget

问题2：

[root@ma-2 mysql]# ./scripts/mysql_install_db –user=mysql –datadir=/data/mysql 

-bash: ./scripts/mysql_install_db: /usr/bin/perl: 坏的解释器: 没有那个文件或目录

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

[root@ma-2 mysql]# yum install -y perl perl-devel

 

[root@ma-2 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@ma-2 mysql]# yum install -y autoconf

 

[root@ma-2 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@ma-2 mysql]# yum -y install libaio-devel

配置主

修改配置文件

[root@ma-1 ~]# vi /etc/my.cnf

增加如下配置

server-id=135

log_bin=shuailinux1

完成配置后，重启MySQL服务

[root@ma-1 ~]# /etc/init.d/mysqld restart

查看MySQL的库文件

[root@ma-1 ~]# 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 ma-1.err

-rw-rw----. 1 mysql mysql 5 1月 29 16:07 ma-1.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@ma-1 mysql]# mysqldump -uroot -p111111 zrlog > /tmp/zrlog.sql

 

新建一个数据库

[root@ma-1 mysql]# mysql -uroot -p111111 -e "create database shuai"

Warning: Using a password on the command line interface can be insecure.

将备份的数据恢复到新建的数据库中

[root@ma-1 mysql]# mysql -uroot -p111111 shuai < /tmp/zrlog.sql

Warning: Using a password on the command line interface can be insecure.

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

[root@ma-1 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@ma-1 mysql]# mysqldump -uroot -p111111 shuai > /tmp/shuai.sql

Warning: Using a password on the command line interface can be insecure.

配置从

编辑配置文件

[root@ma-2 mysql]# vim /etc/my.cnf

 

server-id=134

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

重启

[root@ma-2 mysql]# /etc/init.d/mysqld restart

Shutting down MySQL..... SUCCESS!

Starting MySQL............................................................... SUCCESS!

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

[root@ma-2 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@ma-2 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@ma-2 mysql]# mysql -uroot shuai < /tmp/shuai.sql

[root@ma-2 mysql]# mysql -uroot zrlog < /tmp/zrlog.sql

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

实现主从同步：

在从数据库上

[root@ma-2 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@ma-1 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）。