IPB

Welcome Guest ( Log In | Register )

> [mysql]无锁创建 mysql 复制库
猫猫草
post 2019-10-12 16:57:07, Sat
Post #1


猫猫猫
***

Group: Power Cat
Posts: 626
Joined: 2006-12-8
Member No.: 2



首先确保主库已经启用 server-id 和 log-bin
在 /etc/my.cnf 或对应配置文件中确保以下内容存在
代码
[mysqld]
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id=1

# binary logging format - mixed recommended
binlog-format   = mixed

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

innodb_flush_log_at_trx_commit=1
sync_binlog=1

在主库创建复制用户
代码
CREATE USER replicant@<<slave-server-ip>>;
GRANT REPLICATION SLAVE ON *.* TO replicant@<<slave-server-ip>> IDENTIFIED BY '<<choose-a-good-password>>';

然后使用 mysqldump 无锁提取数据,参数定义参阅 mysqldump 帮助,中途可以修改数据,不能修改库表结构
代码
mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 --all-databases  --host=<hostname> --user=<user> --password=<pass> > ~/dump.sql

获取主库日志点信息
代码
head dump.sql -n80 | grep "MASTER_LOG_POS"

然后复制 dump.sql 到从库服务器,操作从略

确认从库在 /etc/my.cnf 或对等配置文件中启用了 server-id 和相关配置项,且 server-id 不同于主库
代码
server-id               = 101
binlog-format       = mixed
log_bin                 = slave-101
relay-log               = relay-101
log-slave-updates = 1
read-only               = 1
replicate_ignore_db='',mysql,information_schema
slave_type_conversions=ALL_LOSSY,ALL_NON_LOSSY
#slave_skip_errors=1677,1709

对从库还原数据,在从库服务器执行
代码
mysql -u root -p < ~/dump.sql

配置从库复制源
代码
CHANGE MASTER TO MASTER_HOST='<<master-server-ip>>',MASTER_USER='replicant',MASTER_PASSWORD='<<slave-server-password>>', MASTER_LOG_FILE='<<value from above>>', MASTER_LOG_POS=<<value from above>>;
START SLAVE;

检查从库复制源信息
代码
SHOW SLAVE STATUS

启用复制
代码
STOP SLAVE;SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;START SLAVE;
Go to the top of the page
 
+Quote Post

Posts in this topic
猫猫草   [mysql]无锁创建 mysql 复制库   2019-10-12 16:57:07, Sat


Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 



Lo-Fi Version Time is now: 2024-12-25 02:26