Mysql 5.7.21 设置主从库同步

主从复制条件：

Mysql 单机多实例安装参考Mysql 5.7.21 设置主从库同步

下面的操作是多实例主从复制，3306为主库，3307为从库。

主库要开启log-bin，主库和从库的server-id要不一样，修改/etc/my.cnf配置

[mysqld_multi]

mysqld    = /usr/local/mysql/bin/mysqld

mysqladmin = /usr/local/mysql/bin/mysqladmin

log        = /tmp/mysql_multi.log

user = mysql

pass = 123456

[mysqld1]

   datadir = /usr/local/data

   socket = /tmp/mysql.sock1

   port = 3306

   user = mysql

   performance_schema = off

   innodb_buffer_pool_size = 32M

   bind_address = 0.0.0.0

   skip-name-resolve = 0

   server-id=1

   log-bin=/usr/local/data/mysql-bin

[mysqld2]

   datadir = /usr/local/data3307

   socket = /tmp/mysql.sock2

   port = 3307

   user = mysql

   performance_schema = off

   innodb_buffer_pool_size = 32M

   bind_address = 0.0.0.0

   skip-name-resolve = 0

   server-id=2

在主库上面创建同步用户：

mysql>grant replication slave on *.* to 'rep'@'%' identified by '123456';    #后面的密码要设置复杂些。

mysql> flush privileges;

导出主库数据文件：

3.1 常规方法，进入主库：

mysql> flush table with read lock;     #先锁表，锁表后不能退出mysql窗口，否则失效

mysql> show master status;   #记录bin-log的位置信息

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000004 |      560 |              |                  |

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

mysql> exit

mysqldump -uroot -p -S /data/3306/mysql.sock --events -A -B|gzip >/tmp/bak_$(date +%F).sql.gz

mysql> unlock tables;   #解锁数据库

3.2 快捷方法：

mysqldump -uroot -p -S /data/3306/mysql.sock --events -A -B -F --master-data=2 -x|gzip >/tmp/bak_f_$(date +%F).sql.gz

 -x 自动锁表

 -F --master-data=2    以备注形式记录bin-log的位置。（可以打开文件看头部备注信息）

还原备份内容到从库：

gzip -d bak_f_2018-03-26.sql.gz

mysql -uroot -p -S /tmp/mysql.sock2 < bak_f_2018-03-26.sql

连接从库，并执行：mysql -uroot -p123456 -S /tmp/mysql.sock2

CHANGE MASTER TO

MASTER_HOST='172.16.1.214',

MASTER_PORT=3306,

MASTER_USER='rep',

MASTER_PASSWORD='Root1234',

MASTER_LOG_FILE='mysql-bin.000004',

MASTER_LOG_POS=560;

mysql> start slave;        #开启同步

mysql> show slave status \G ;    #查看同步状态信息

监控mysql主从复制的状态指标：

mysql -uroot -p123456 -S /data/3307/mysql.sock -e "show slave status \G ;" | egrep -i "_Running|_Behind"

        Slave_IO_Running: Yes                             #同步进程是否启动

        Slave_SQL_Running: Yes                          #sql进程是否启动

        Seconds_Behind_Master: 0                       #从库同步完成需要的时间

巴特西

Mysql 5.7.21 设置主从库同步

最新文章

热门文章