mysql如何搭建备库_mysql主从库搭建

1、主备库的搭建：

略主库：10.1.1.17

备库：10.1.1.18

2、主库授权：

允许复制备份的权限GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'10.1.1.18' IDENTIFIED BY 'hsdhsT56';

FLUSH PRIVILEGES

3、主库数据导出mysqldump  -uroot -p  -h 127.0.0.1 --databases TestDB --default-character-set=utf8 --net_buffer_length=16535 --single-transaction --master-data=2   >TestDB.sql

如果该机器上有多个库，可以将所有库都导出mysqldump  -uroot -p  -h 127.0.0.1 --all-databases --default-character-set=utf8 --net_buffer_length=16535 --max_allowed_packet=1048576 --single-transaction --master-data=2   >all_databases.sql

参数说明：因为我们这里是数据备份出来并作为slave导入，所以需要--single-transaction --master-data搭配使用，如果只是单纯导出数据可以只使用--single-transaction

net_buffer_length：

TCP/IP和套接字通信缓冲区大小,创建长度达net_buffer_length的行，如果这个值比目标数据库的值大，可能会出错。

--max_allowed_packet=1048576

客户端/服务器之间通信的缓存区的最大大小,使用这个参数，主要是为加快导出

使用的时候我将该参数设置到了--max_allowed_packet=21048576才把我的13G数据导出时间从8分钟缩短为7分钟。。。

single-transaction：

InnoDB 表在备份时，通常启用选项 --single-transaction 来保证备份的一致性，实际上它的工作原理是设定本次会话的隔离级别为：REPEATABLE READ，以确保本次会话(dump)时，不会看到其他会话已经提交了的数据。

master-data=2:

If the option value is 2, the CHANGE MASTER TO statement is written as an SQL comment, and thus is informative only; it has no effect when the dump file is reloaded. If the option value is 1, the statement takes effect when the dump file is reloaded. If the option value is not specified, the default value is 1.

意思是说：如果master-data=1，那么CHANGE MASTER会被写到语句中，如果是master-data=2，那么CHANGE MASTER只是作为注释写到内容中。这里我们使用2，如果使用1，那么数据导入后，CHANGE MASTER立即就运行了。我们希望CHANGE MASTER，由我们手工控制。

4、以上数据导入备库mysql -uroot -p  -e --max_allowed_packet=1048576 --net_buffer_length=16384 

5、

查看应该从哪个位置开始同步grep -i "change master" TestDB.sql

MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=106这里就说明了，我这次数据导出的末尾得主库日志为mysql-bin.000004，位置为106。那么我把备库起来之后，就需要从同步此位置之后的数据

6、开始同步--停止slave

STOP SLAVE

--CHANGE MASTER到主库

CHANGE MASTER TO MASTER_HOST='10.1.1.17',MASTER_USER='slaveuser',MASTER_PASSWORD='hsdhsT56',MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=106;

--开启slave

START SLAVE;

查看slave状态

SHOW SLAVE STATUS;