本站主要内容均为原创,转帖需注明出处www.alexclouds.net
需要补充 mysql5.7的AA复制了,项目需要。
以前写过5.6的主从复制,但是猛然发现5.7.8的好处,是速度比5.6快,是的,你没看错,不过是ORACLE官方数据说快一倍,也许没有那么快。
有三个主要特点优势比5.6:
1、MySQL 5.7 修改了半同步中主库提交的事务的顺序,after sync 模式避免了幻读发生。
2、MySQL 5.7 半同步增强,增加 rpl_semi_sync_master_wait_slave_count 参数控制主库接收多少个slave 写事务成功反馈才返回成功给客户端 。
3、原来有dump thread 发送event和接收slave ack 模式,独立出 单独 接收slave 返回 ack的进程,提高半同步模式的tps 。
mysql> select host,user,authentication_string from user;
+-----------------+------+-------------------------------------------+
| host | user | authentication_string |
+-----------------+------+-------------------------------------------+
| localhost | root | *CF855080CC9EF389F5967EFBA2384DA9094AF7B3 |
| 118.242.237.108 | user | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------------+------+-------------------------------------------+
2 rows in set (0.00 sec)
masters上
授予权限:
GRANT ALL PRIVILEGES ON *.* TO 'user'@'118.242.237.108' identified by '123456';
FLUSH PRIVILEGES;
/etc/my.cnf
[mysqld]
user = mysql
log-bin = mysql-bin
server-id = 1
binlog-do-db = dns
binlog-ignore-db = mysql
replicate-do-db = dns
replicate-ignore-db = mysql
log-slave-updates
slave-skip-errors = all
sync_binlog = 1
auto_increment_increment = 2
auto_increment_offset = 1
log_slow_queries = /var/log/mysql-slow.log
mysql> show variables like "%log_%";
+------------------------------------------+-------------------------------------------+
| Variable_name | Value |
+------------------------------------------+-------------------------------------------+
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| expire_logs_days | 0 |
| general_log_file | /usr/local/mysql/data/ns1.log |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_log_buffer_size | 16777216 |
| innodb_log_checksum_algorithm | innodb |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_log_write_ahead_size | 8192 |
| innodb_max_undo_log_size | 1073741824 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_undo_log_truncate | OFF |
| innodb_undo_logs | 128 |
| log_backward_compatible_user_definitions | OFF |
| log_bin | ON |
| log_bin_basename | /usr/local/mysql/data/mysql-bin |
| log_bin_index | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_error | /usr/local/mysql/data/ns1.e-green.org.err |
| log_error_verbosity | 3 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | ON |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_syslog | OFF |
| log_syslog_facility | daemon |
| log_syslog_include_pid | ON |
| log_syslog_tag | |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | UTC |
| log_warnings | 2 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_relay_log_size | 0 |
| relay_log_basename | |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_info_repository | FILE |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| slow_query_log_file | /usr/local/mysql/data/ns1-slow.log |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sync_relay_log_info | 10000 |
+------------------------------------------+-------------------------------------------+
63 rows in set, 1 warning (0.00 sec)
>flush logs;
看master状态,获取Position值,但是获取之后请勿操作MYSQL,否则大麻烦。
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 967 | dns | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 967 |
+------------------+-----------+
1 row in set (0.00 sec)
重置主的master状态:
mysql> reset master;
slave上:
GRANT ALL PRIVILEGES ON *.* TO 'user'@'118.242.237.106' identified by '123456';
FLUSH PRIVILEGES;
/etc/my.cnf
[mysqld]
user = mysql
log-bin = mysql-bin
server-id = 2
binlog-do-db = dns
binlog-ignore-db = mysql
replicate-do-db = dns
replicate-ignore-db = mysql
log-slave-updates
slave-skip-errors = all
sync_binlog = 1
auto_increment_increment = 2
auto_increment_offset = 2
配置从服务器Slave:
change master to
master_host='118.242.237.106',
master_user='user',
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=967;
启动从服务器复制功能 Mysql>start slave;
检查从服务器复制功能状态:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 118.242.237.106
Master_User: user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1796
Relay_Log_File: ns2-relay-bin.000008
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: dns
Replicate_Ignore_DB: mysql
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: 1796
Relay_Log_Space: 525
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: 190e7a4b-5ddf-11e5-9899-005056a19a19
Master_Info_File: /usr/local/mysql-5.7.8-rc-linux-glibc2.5-x86_64/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for 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:
1 row in set (0.00 sec)
mysql>
Slave_IO及Slave_SQL进程必须正常运行,即YES状态
做之前把两边数据库同步一样,复制过去解压覆盖从库。
cd /usr/local/mysql
tar -zcf mysql.slave.tar.gz data/
scp –Pxxxx mysql.slave.tar.gz root@118.242.237.108:/usr/local/mysql
tar -zxvf mysql.slave.tar.gz