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/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 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; 另外有需要云服务器可以了解下创新互联cdcxhl.cn,海内外云服务器15元起步,三天无理由+7*72小时售后在线,公司持有idc许可证,提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案,具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势,专为企业上云打造定制,能够满足用户丰富、多元化的应用场景需求。
标题名称:mysql主从复制原理-创新互联
分享URL:http://myzitong.com/article/dpjggh.html