mysql的主从配置

 

搭建主从复制主要条件

主库的serverid要不一致
log_bin=mysql_bin
binlog-format=Row
server-id=1

在主库上创建一个主从复制的账号

root@db 23: 30:   [(none)]>create user 'bak'@'192.168.1.%' identified by '123456';

root@db 23: 31:   [(none)]>grant replication slave on *.* to 'bak'@'192.168.1.%';

root@db 23: 32:   [(none)]>flush pri

获取主库的日志信息
主库表加锁
mysql>flush tables with read lock;
mysql>show master status;
+------------+----------+--------------+------------------+-------------------------------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------+----------+--------------+------------------+-------------------------------------------+
| bin.000005 |      805 |              |                  | 8472ab1c-dfc8-11e8-b1c8-000c2920c8ee:1-79 |
+------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)


主库备份
一种压缩备份
[root@ce-7-5 ~]# mysqldump --single-transaction -uroot -p  -A |gzip> /tmp/db_`date +%F`.sql.gz
宁外一种不压缩备份
[root@ce-7-5 mysql]# mysqldump --single-transaction -uroot -p  -A > /tmp/db_`date +%F`.sql

在从库上
将备份的文件传送到slave主机上
root@ce-7-5 mysql]# scp /tmp/db_2019-01-16.sql root@192.168.1.157:/tmp/
在从库上
mysql>source /tmp/db_2019-01-16.sql



root@db 00: 44:   [(none)]>CHANGE MASTER TO
    -> MASTER_HOST='192.168.1.158',
    -> MASTER_PORT=3306,
    -> MASTER_USER='bak',
    -> MASTER_PASSWORD='123456',
    -> MASTER_LOG_FILE='bin.000005',
    -> MASTER_LOG_POS=805;
应为
CHANGE MASTER TO
MASTER_HOST='192.168.1.158',
MASTER_PORT=3306,
MASTER_USER='bak',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='bin.000005',
MASTER_LOG_POS=805;
root@db 01: 08:   [(none)]>start slave;



root@db 01: 09:   [(none)]>show slave status\G
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 Seconds_Behind_Master: 0


在主库上
root@db 18: 23:   [(none)]>UNLOCK TABLES;


自此复制完成