[mysql]无锁创建 mysql 复制库 |
[mysql]无锁创建 mysql 复制库 |
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;
|
|
|
Lo-Fi Version | Time is now: 2024-10-31 09:38 |