mysql主从和dump_mysqldump主从复制

一，主从复制条件

1，开启binlog功能

2，主库建立同步账号

3，从库配置master。info(change mast。。)

4，start slave复制开关

知识点：####

1,  3个线程，主库io，从库io和sql

2，master。info作用

3，relay-log作用

4.  异步复制

5.  binlog作用

二，部署主从复制

1。检查binlog是否开启，如果没开启在my.cnf里添加这段，重启数据库(把注释#去掉)

egrep "log-bin|server-id" /data/{3306,3307}/my.cnf

/data/3306/my.cnf:#log-bin = /data/3306/mysql-bin

/data/3306/my.cnf:server-id = 1

/data/3307/my.cnf:#log-bin = /data/3307/mysql-bin

/data/3307/my.cnf:server-id = 2

2。重启数据库(此处用的是多实例3306和3307)

/data/3306/mysql restart

/data/3307/mysql restart

3.主库授权复制的用户rep

mysql -uroot -p123456 -S /data/3306/mysql.sock

mysql>grant replication slave on *.* to rep@'172.16.1.%' identified by '123456';

4.锁表查看binlog位置点 (锁表可以用--master-data=2)

flush table with read lock;

mysql> show master status; 查看主库状态，及binlog位置点

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000002 | 1680 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

5.新开窗口导出全备

mkdir /server/backup -p 创建备份目录

mysqldump -uroot -p123456 -A -B --events -S /data/3306/mysql.sock|gzip >/server/backup/rep_bak_$(date +%F).sql.gz

6.解锁开放用户写入

unlock table; binlog会变化，但是不影响因为没数据那段

7.把备份的数据同步到从库服务器(由于此次试验环境实在一台机器多实例做的，左右不需要一下这条命令。如果是两台物理机或者两台虚拟机那么要下边的命令)

scp /server/backup/rep_bak_2016-11-03.sql.gz root@172.16.1.51:~

三，从库操作

1，把主库的全备导入从库

cd /server/backup/

gzip -d rep_bak_2016-11-03.sql.gz

mysql -uroot -p123456 -S /data/3306/mysql.sock

2，找主库binlog位置点。(主库操作)

mysql> show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000003 | 196 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

3，配置master.info(从库操作)

CHANGE MASTER TO

MASTER_HOST='172.16.1.51',

MASTER_PORT=3306,

MASTER_USER='rep',

MASTER_PASSWORD='123456',

MASTER_LOG_FILE='mysql-bin.000003', #######这里需要主库的binlog

MASTER_LOG_POS=196;

4，查看master.info配置文件内容

find /data/3306/data/ -type f -name "*.info"

vim /data/3306/data/master.info

1 18

2 mysql-bin.000003

3 196

4 172.16.1.50

5 rep

6 123456

7 3306

8 60

9 0

10

11

12

13

14

15 0

16 1800.000

17

18 0

5，开启从库开关

start slave;

6，查看同步状态

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.1.50

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 196

Relay_Log_File: relay-bin.000002

Relay_Log_Pos: 253

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB: mysql

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: 196

Relay_Log_Space: 403

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: 1

1 row in set (0.00 sec)

7，主从的两个线程

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

8，是否有延迟

Seconds_Behind_Master: 0

9，可以测试结果

主库增删改，会同步到从库，

但是从库增删改，不会影响主库