ssh auto mysqld 实时数据 配置 均衡 t_sql ges
同步原理:
配置过程:
配置:
master 3307
slave 3308
================================================== 主库 ============================================
1. 开启 bin-log,保证主备库的 server-id 不相同
2. bin-log 要放在 [mysqld] 模块下面
3. 检查主库的 server_id
- [[email protected] ~]# mysql-uroot-proot-S/data/3307/mysql.sock-e "show variableslike 'server_id'";
- +---------------+-------+
- |Variable_name|Value|
- +---------------+-------+
- |server_id| 1 |
- +---------------+-------+
4. 检查主库是否开启 log_bin,如果没有开启,修改 my.cnf 文件进行添加:
- [[email protected] ~]# mysql-uroot-proot-S/data/3307/mysql.sock-e "show variableslike 'log_bin%'";
- +---------------------------------+---------------------------------+
- |Variable_name|Value|
- +---------------------------------+---------------------------------+
- |log_bin| ON |
- |log_bin_basename| /data/3307/data/mysql-bin|
- |log_bin_index| /data/3307/data/mysql-bin.index |
- |log_bin_trust_function_creators| OFF |
- |log_bin_use_v1_row_events| OFF |
- +---------------------------------+---------------------------------+
5. 连接到主库,创建用户,可以从备库登陆过来进行日志读取:
[[email protected] ~]# mysql -uroot -proot -S /data/3307/mysql.sock
- mysql > grant replication slave on * . * to'rep'@'192.168.56.99'identified by'abbott';
- Query OK,
- 0 rows affected,
- 1 warning(0.01 sec)
- mysql > flush privileges;
- Query OK,
- 0 rows affected(0.01 sec)
6. 锁定主库的表,然后进行备份,否则备份集将不成功
- mysql > flush table with read lock;
- Query OK,
- 0 rows affected(0.00 sec)
7. 查看此时的 position,确保备份完成之后不会改变
- mysql> show master status;
- +------------------+----------+--------------+------------------+-------------------+
- | File |Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
- +------------------+----------+--------------+------------------+-------------------+
- |mysql-bin.000003 | 600 | | | |
- +------------------+----------+--------------+------------------+-------------------+
- 1rowin set(0.00sec)
8. 查看 log
- mysql> show master logs;
- +------------------+-----------+
- |Log_name|File_size|
- +------------------+-----------+
- |mysql-bin.000001 | 727 |
- |mysql-bin.000002 | 833 |
- |mysql-bin.000003 | 600 |
- +------------------+-----------+
- 3rowsin set(0.00sec)
9. 对主库进行备份
- [[email protected] log]# mysqldump-uroot-proot-S/data/3307/mysql.sock-A-B--events --master-data=2 > /opt/rep.sql
- [[email protected] opt]# mysqldump-uroot-proot-S/data/3307/mysql.sock-A-B--master-data=1 --single-transaction >/opt/full.sql
- 企业推荐:不停库(使用这种方式,不需要指定binlog位置,也不需要锁表,--single-transaction参数可以屏蔽其余会话的增删改,即不需要加master_log_file和master_log_pos这两个参数)
10. 检查备份后 position 是否改变
- mysql> show master status;
- +------------------+----------+--------------+------------------+-------------------+
- | File |Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
- +------------------+----------+--------------+------------------+-------------------+
- |mysql-bin.000003 | 600 | | | |
- +------------------+----------+--------------+------------------+-------------------+
- 1rowin set(0.00sec)
11. 如果检查没有问题,那么就取消锁定
- mysql > unlock tables;
- Query OK,
- 0 rows affected(0.00 sec)
+++++++++++++++++++++++++++++++++++++++++++++++++++ 备库 ++++++++++++++++++++++++++++++++++++++++++++++++++++
12. 在备库恢复备份的文件
- [[email protected] opt]# mysql-uroot-proot-S/data/3308/mysql.sock</opt/rep.sql
13. 查看备份是否恢复成功
- [[email protected] opt]# mysql-uroot-proot-S/data/3308/mysql.sock
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- |information_schema|
- |ZX|
- |abbott|
- |mysql|
- |performance_schema|
- |sys|
- +--------------------+
- 6rowsin set(0.00sec)
14. 指定备库同步主库的信息
- change master to MASTER_HOST = '192.168.56.99',
- MASTER_PORT = 3307,
- MASTER_USER = 'rep',
- MASTER_PASSWORD = 'abbott',
- MASTER_LOG_FILE = 'mysql - bin.000003',
- MASTER_LOG_POS = 600;
- Query OK,
- 0 rows affected,
- 2 warnings(0.13 sec)
15. 查看 master_info 里记录的信息是否和指定同步的位置一样
- [[email protected] data]# cat master.info
- 24
- mysql-bin.000003
- 600
- 192.168.56.99
- rep
- abbott
- 3307
16. 备库开始进行同步
- mysql > start slave;
- Query OK,
- 0 rows affected(0.01 sec)
17. 查看同步情况,其中参数 Slave_IO_Running 和 Slave_SQL_Running 都是 yes。
- mysql> show slave status\G
- *************************** 1. row***************************
- Slave_IO_State: Waiting formasterto send event
- Master_Host: 192.168.56.99
- Master_User: rep
- Master_Port: 3307
- Connect_Retry: 60
- Master_Log_File: mysql-bin.000003
- Read_Master_Log_Pos: 600
- Relay_Log_File: MySQL-relay-bin.000002
- Relay_Log_Pos: 320
- Relay_Master_Log_File: mysql-bin.000003
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
- Replicate_Do_DB:
- Replicate_Ignore_DB:
- Replicate_Do_Table:
- Replicate_Ignore_Table:
- Replicate_Wild_Do_Table:
- Replicate_Wild_Ignore_Table:
- Last_Errno: 0
- Last_Error:
- Skip_Counter: 0
- Exec_Master_Log_Pos: 600
- Relay_Log_Space: 527
- Until_Condition: None
- Until_Log_File:
- Until_Log_Pos: 0
- Master_SSL_Allowed: No
- Master_SSL_CA_File:
- Master_SSL_CA_Path:
- Master_SSL_Cert:
- Master_SSL_Cipher:
- Master_SSL_Key:
- Seconds_Behind_Master: 0
- Master_SSL_Verify_Server_Cert: No
- Last_IO_Errno: 0
- Last_IO_Error:
- Last_SQL_Errno: 0
- Last_SQL_Error:
- Replicate_Ignore_Server_Ids:
- Master_Server_Id: 1
- Master_UUID: 3b9f6e49-5a6b-11e7-b603-08002714955b
- Master_Info_File: /data/3308/data/master.info
- SQL_Delay: 0
- SQL_Remaining_Delay: NULL
- Slave_SQL_Running_State: Slave has read allrelaylog; waitingfor more updates
- Master_Retry_Count: 86400
- Master_Bind:
- Last_IO_Error_Timestamp:
- Last_SQL_Error_Timestamp:
- Master_SSL_Crl:
- Master_SSL_Crlpath:
- Retrieved_Gtid_Set:
- Executed_Gtid_Set:
- Auto_Position: 0
- Replicate_Rewrite_DB:
- Channel_Name:
- 1rowin set(0.00sec)
18. 测试,在主库创建一个库 abc,可以看到在备库已经同步过来。
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- |information_schema|
- |ZX|
- |abbott|
- |abc|
- |mysql|
- |performance_schema|
- |sys|
- +--------------------+
- 7rowsin set(0.00sec)
查看线程状态:
主库:
- mysql> show processlist;
- +----+------+---------------------+--------+-------------+------+---------------------------------------------------------------+------------------+
- |Id| User |Host|db|Command|Time|State|Info|
- +----+------+---------------------+--------+-------------+------+---------------------------------------------------------------+------------------+
- | 9 |rep| 192.168.56.99:38359 | NULL |BinlogDump | 215 |Master has sentallbinlogtoslave; waitingformore updates| NULL |
- | 10 |root|localhost|abbott|Query| 0 |starting|show processlist|
- +----+------+---------------------+--------+-------------+------+---------------------------------------------------------------+------------------+
- 2rowsin set(0.00sec)
备库:
- mysql> show processlist;
- +----+-------------+-----------+--------+---------+------+--------------------------------------------------------+------------------+
- |Id| User |Host|db|Command|Time|State|Info|
- +----+-------------+-----------+--------+---------+------+--------------------------------------------------------+------------------+
- | 15 |root|localhost|abbott|Query| 0 |starting|show processlist|
- | 16 |systemuser | | NULL |Connect| 237 |Waitingformastertosend event| NULL |
- | 17 |systemuser | | NULL |Connect| 144 |Slave hasread allrelaylog; waitingformore updates| NULL |
- +----+-------------+-----------+--------+---------+------+--------------------------------------------------------+------------------+
- 3rowsin set(0.00sec)
要点:
生产场景快速配置 mysql 主从复制方案
mysql 其他主从复制实现:
主服务器挂了,人工或自动切换到从服务器:http://oldboy.blog.51cto.com/2561410/1240412
可以保证同步的方式:
读写分离:
mysql 主从复制配置
来源: http://www.bubuko.com/infodetail-2145141.html