mysql5.7 主从同步配置
英文好的可以直接去mysql官网查看 https://dev.mysql.com/doc/refman/5.7/en/replication.html
1 环境准备
我这里准备两套linux虚拟机，主mysql服务器（Ubuntu18.04.4 TLS Server）,从mysql 服务区（centos 7）
Ubuntu18.04.4 TLS Server ip 192.168.1.30
centos 7 ip 192.168.1.100
1.1 备份主mysql数据库
1.2 从数据库恢复主mysql数据库
1.3 配置主mysql服务器
配置server-id
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
log_bin = /var/log/mysql/logbin.log
log_bin_trust_function_creators = 1
# binlog忽略的数据库
binlog-ignore-db = mysql
# master
# server-id要唯一，这里取ip段最后一段
server-id = 30
skip-grant-tables
# By default we only accept connections from localhost
# #bind-address = 127.0.0.1
# # Disabling symbolic-links is recommended to prevent assorted security risks
# symbolic-links=0
# lower_case_table_names = 1
# wait_timeout=31536000
# interactive_timeout=315360000
#
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
# !includedir /etc/mysql/conf.d/
# !includedir /etc/mysql/mysql.conf.d/
重启mysql 服务
service mysql restart
1.4 查看配置
查看bin_log配置
mysql> show global variables like '%log%';
+--------------------------------------------+------------------------------------+
| Variable_name | Value |
+--------------------------------------------+------------------------------------+
| back_log | 80 |
| 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 |
| binlog_transaction_dependency_history_size | 25000 |
| binlog_transaction_dependency_tracking | COMMIT_ORDER |
| expire_logs_days | 0 |
| general_log | OFF |
| general_log_file | /var/lib/mysql/zmh.log |
| innodb_api_enable_binlog | OFF |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 16777216 |
| innodb_log_checksums | ON |
| 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_bin | ON |
| log_bin_basename | /var/log/mysql/logbin |
| log_bin_index | /var/log/mysql/logbin.index |
| log_bin_trust_function_creators | ON |
| log_bin_use_v1_row_events | OFF |
| log_builtin_as_identified_by_password | OFF |
| log_error | /var/log/mysql/error.log |
| log_error_verbosity | 3 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| 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 | |
| relay_log_basename | /var/lib/mysql/zmh-relay-bin |
| relay_log_index | /var/lib/mysql/zmh-relay-bin.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 | OFF |
| slow_query_log_file | /var/lib/mysql/zmh-slow.log |
| sql_log_off | OFF |
| sync_binlog | 1 |
| sync_relay_log | 10000 |
| sync_relay_log_info | 10000 |
+--------------------------------------------+------------------------------------+
73 rows in set (0.00 sec)
1.5 创建用于从服务器同步数据使用的帐号
mysql –uroot –pmysql
CREATE USER 'copy'@'192.168.1.100' IDENTIFIED BY 'copy';
GRANT REPLICATION SLAVE ON *.* TO 'copy'@'192.168.1.100';
FLUSH PRIVILEGES;
1.6 获取主服务器的二进制日志信息
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| logbin.000003 | 154 | | mysql | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
File为使用的日志文件名字，Position为使用的文件位置，这两个参数须记下，配置从服务器时会用到
1.7 配置从服务器(centos 7)
[mysqld]
# server-id 这里为我的ipv4地址的最后一段
server-id=100
# 开启mysql binlog功能
log-bin = /var/lib/mysql/mysql-bin
# binlog记录内容的方式，记录被操作的每一行
binlog_format = ROW
# 减少记录日志的内容，只记录受影响的列
binlog_row_image = minimal
# 指定需要复制的数据库名为wicket8
replicate-do-db = wicket8
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysqld_safe]
重启服务
[root@localhost etc]# systemctl restart mysqld.service
设置主服务器
CHANGE MASTER TO MASTER_HOST='192.168.1.30',MASTER_USER='copy',MASTER_PASSWORD='copy';
CHANGE MASTER 详细用法（根据实际情况设置偏移量）
https://dev.mysql.com/doc/refman/5.7/en/change-master-to.html
开启服务
start slave;
