mysql主从复制原则
发布时间：2022-06-25 00:15:57 所属栏目：MySql教程 来源：互联网
导读：环境： 3306 主库 3307 从库 1 设置server-id值并开启binlog功能参数 编辑mysql的配置文件/data/3306/my.cnf [mysqld] server-id = 6 ----用于同步的每台机器或实例sever-id都不能相同 log_bin =/data/3306/mysql-bin ----可以省略 重启服务 /data/3306/mysq
|
环境： 3306 主库 3307 从库 1 设置server-id值并开启binlog功能参数 编辑mysql的配置文件/data/3306/my.cnf [mysqld] server-id = 6 ----用于同步的每台机器或实例sever-id都不能相同 log_bin =/data/3306/mysql-bin ----可以省略 重启服务 /data/3306/mysql restart 检查思路一 [root@db02 data]# egrep "log_bin|server-id" 330*/my.cnf 3306/my.cnf:log_bin = /data/3306/mysql-bin 3306/my.cnf:server-id = 6 3307/my.cnf:server-id = 7 检查思路二： 检查思路2： 登录： [root@db02 data]# mysql -S /data/3306/mysql.sock 查看变量： mysql> show variables like 'log_bin%'; +---------------------------------+----------------------------+ | Variable_name | Value | +---------------------------------+----------------------------+ | log_bin | ON | | log_bin_basename | /data/3306/mysql-bin | | log_bin_index | /data/3306/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | +---------------------------------+----------------------------+ 5 rows in set (0.00 sec) 2 建账号授权【主库】 grant replication slave on *.* to 'rep'@'172.16.1.%' identified by 'oldboy123'; flush privileges; 3锁表导出数据 mysql> flush table with read lock; Query OK, 0 rows affected (0.00 sec) 查看位置： mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 405 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 新开窗口备份： mysqldump -uroot -p'oldboy123' -S /data/3306/mysql.sock -A -B |gzip >/server/backup/mysql_bak.$(date +%F).sql.gz 原窗口解锁： mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 405 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> unlock tables; Query OK, 0 rows affected (0.01 sec) 如下命令可替代3的所有步骤 mysqldump -uroot -p'oldboy123' --master-data=2 -S /data/3306/mysql.sock -A -B 4、将数据导入到从库 [root@db02 backup]# mysql -S /data/3307/mysql.sock <mysql_bak.2017-05-04.sql 5、让从库从主库锁表时刻记录的binlog位置点开始向下同步 CHANGE MASTER TO MASTER_HOST='172.16.1.52', MASTER_PORT=3306, MASTER_USER='rep', MASTER_PASSWORD='oldboy123', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=405; （编辑：济源站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
