MySQL主从复制（异步模式） -云博客

MySQL主从复制（异步模式）

# 数据库 2024-05-05 23:01 0 134 来源：云博客

MySQL主从复制有异步模式、半同步模式、GTID模式以及多源复制模式，MySQL默认模式是异步模式。所谓异步模式，只MySQL 主服务器上I/O thread 线程将二进制日志写入binlog文件之后就返回客户端结果，不会考虑二进制日志是否完整传输到从服务器以及是否完整存放到从服务器上的relay日志中，这种模式一旦主服务(器)宕机，数据就会发生丢失。

环境：

1 [root@localhost ~]# cat /etc/redhat-release2 CentOS Linux release 7.2.1511 (Core)3 [root@localhost ~]# uname -a4 Linux localhost.localdomain 3.10.0-327.el7.x86_64 #1 SMP Thu Nov 19 22:10:57 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux5 [root@localhost ~]#

View Code

MySQL版本：

mysql-5.7.22-linux-glibc2.12-x86_64.tar

主从复制IP规划：

主：192.168.112.2220

从：192.168.112.221

部署步骤：

tar xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gzmkdir -p /applicationmv mysql-5.7.22-linux-glibc2.12-x86_64 /application/ln -s /application/mysql-5.7.22-linux-glibc2.12-x86_64 /application/mysqlgroupadd mysqluseradd mysql -g mysql -s /sbin/nologinmkdir -p /application/datachown -R mysql.mysql /application/mysqlchown -R mysql.mysql /application/mysql/*chown -R mysql.mysql /application/data

安装常用依赖库：

[root@localhost ~]# yum install cmake gcc gcc-c++ libaio-devel automake autoconf bison libtool ncurses-devel libgcrypt-devel libev-devel perl-Digest-MD5 perl-DBI perl-DBD-MySQL

编写配置文件my.cnf：

 1 [client] 2 port = 3306 3 socket = /tmp/mysql.sock 4  5 [mysql] 6 prompt="\u@db \R:\m:\s [\d]> " 7 no-auto-rehash 8  9 [mysqld] 10 user = mysql 11 port = 3306 12 basedir = /application/mysql 13 datadir = /application/data/mysql/ 14 socket = /tmp/mysql.sock 15 character-set-server = utf8mb4 16 skip_name_resolve = 1 17 open_files_limit = 65535 18 back_log = 1024 19 max_connections = 512 20 max_connect_errors = 100000 21 table_open_cache = 1024 22 table_definition_cache = 1024 23 thread_stack = 512K 24 external-locking = FALSE 25 max_allowed_packet = 32M 26 sort_buffer_size = 4M 27 join_buffer_size = 4M 28 thread_cache_size = 0 29 query_cache_size = 0 30 query_cache_type = 0 31 interactive_timeout = 600 32 wait_timeout = 600 33 tmp_table_size = 32M 34 slow_query_log =1 35 slow_query_log_file = /application/data/mysql/slow.log 36 log-error = /application/data/mysql/error.log 37 long_query_time = 0.5 38 server-id = 33060220 39 log-bin = /application/data/mysql/mysql-binlog 40 sync_binlog = 1 41 binlog_cache_size = 4M 42 max_binlog_cache_size = 1G 43 max_binlog_size = 1G 44 expire_logs_days = 7 45 master_info_repository = TABLE 46 relay_log_info_repository = TABLE 47 #gtid_mode = on 48 enforce_gtid_consistency = 1 49 log_slave_updates 50 binlog_format = row 51 relay_log_recovery = 1 52 relay-log-purge = 1 53 key_buffer_size = 32M 54 read_buffer_size = 8M 55 read_rnd_buffer_size = 4M 56 bulk_insert_buffer_size = 64M 57  58 lock_wait_timeout = 3600 59 explicit_defaults_for_timestamp = 1 60 innodb_thread_concurrency = 0 61 innodb_sync_spin_loops = 100 62 innodb_spin_wait_delay = 30 63 transaction_isolation = REPEATABLE-READ 64 innodb_buffer_pool_size = 1024M 65 innodb_buffer_pool_instances = 8 66 innodb_buffer_pool_load_at_startup = 1 67 innodb_buffer_pool_dump_at_shutdown = 1 68 innodb_data_file_path = ibdata1:1G:autoextend 69 innodb_flush_log_at_trx_commit = 1 70 innodb_log_buffer_size = 32M 71 innodb_log_file_size = 2G 72 innodb_log_files_in_group = 2 73 innodb_io_capacity_max = 2000 74 innodb_io_capacity_max = 4000 75 innodb_flush_neighbors = 0 76 innodb_write_io_threads = 8 77 innodb_read_io_threads = 8 78 innodb_purge_threads = 4 79 innodb_page_cleaners = 4 80 innodb_open_files = 65535 81 innodb_max_dirty_pages_pct = 50 82 innodb_flush_method = O_DIRECT 83 innodb_lru_scan_depth = 4000 84 innodb_checksum_algorithm = crc32 85 innodb_lock_wait_timeout = 10 86 innodb_rollback_on_timeout = 1 87 innodb_print_all_deadlocks = 1 88 innodb_file_per_table = 1 89 innodb_online_alter_log_max_size = 4G 90 internal_tmp_disk_storage_engine = InnoDB 91 innodb_stats_on_metadata = 0 92 innodb_status_file = 1 93 innodb_status_output = 0 94 innodb_status_output_locks = 0 95  96 #performance_schema 97  98 performance_schema = 1 99 performance_schema_instrument = ‘%=on‘100 101 #innodb monitor102 innodb_monitor_enable="module_innodb"103 innodb_monitor_enable="module_server"104 innodb_monitor_enable="module_dml"105 innodb_monitor_enable="module_ddl"106 innodb_monitor_enable="module_trx"107 innodb_monitor_enable="module_os"108 innodb_monitor_enable="module_purge"109 innodb_monitor_enable="module_log"110 innodb_monitor_enable="module_lock"111 innodb_monitor_enable="module_buffer"112 innodb_monitor_enable="module_index"113 innodb_monitor_enable="module_ibuf_system"114 innodb_monitor_enable="module_buffer_page"115 innodb_monitor_enable="module_adaptive_hash"116 117 [mysqldump]118 quick119 max_allowed_packet = 32M

初始化数据操作：

[root@localhost ~]# mysqld --defaults-file=/etc/my.cnf --basedir=/application/mysql --datadir=/application/data/ --user=mysql --initialize

启动数据库：

[root@localhost ~]# /application/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &

创建用于主从复制的用户及账户：

root@db 14:47: [(none)]> create user ‘rep‘@‘192.168.112.%‘ identified by ‘rep123‘;Query OK, 0 rows affected (0.08 sec)root@db 14:51: [(none)]> grant replication slave on *.* to ‘rep‘@‘192.168.112.%‘;Query OK, 0 rows affected (0.01 sec)
root@db 14:52:  [(none)]> flush privileges;
Query OK, 0 rows affected (0.03 sec)root@db 14:52: [(none)]>

导出数据库：

mysqldump --single-transaction -uroot -hlocalhost -proot@123 --master-data=2 -A >all.sql
[root@localhost ~]# netstat -lnupt|grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      5819/mysqld         
[root@localhost ~]#

说明：--single-transaction 如果备份的数据库存储引擎是InnoDB，该参数可以保证数据的一致性，配合RR隔离级别一起使用，当发起事务时，读取一个数据的快照直到备份结束时，都不会读取到本事务开始之后提交的任何数据，--master-data=2，让备份出来的文件中记录备份这一时刻的binlog文件与position号，即记录备份时的binlog文件名和偏移位置

至此，主从复制在主服务器上操作完成，后面在从服务器上操作：

同样部署MySQL服务，然后登陆MySQL，导入all.sql文件，再运行：

CHANGE MASTER TOMASTER_HOST=‘192.168.112.220‘,MASTER_USER=‘rep‘,MASTER_PASSWORD=‘rep123‘,MASTER_PORT=3306,MASTER_LOG_FILE=‘mysql-binlog.000003‘,MASTER_LOG_POS=863;

开启主从复制状态：

root@db 14:55: [(none)]> start slave;

查看主从复制状态：

 1 root@db 14:56: [(none)]> show slave status\G; 2 *************************** 1. row *************************** 3 Slave_IO_State: Waiting for master to send event 4 Master_Host: 192.168.112.220 5  Master_User: bak 6 Master_Port: 3306 7 Connect_Retry: 60 8 Master_Log_File: mysql-binlog.000003 9 Read_Master_Log_Pos: 227410 Relay_Log_File: localhost-relay-bin.00000211 Relay_Log_Pos: 173412 Relay_Master_Log_File: mysql-binlog.00000313  Slave_IO_Running: Yes14  Slave_SQL_Running: Yes15  Replicate_Do_DB: 16  Replicate_Ignore_DB: 17  Replicate_Do_Table: 18  Replicate_Ignore_Table: 19  Replicate_Wild_Do_Table: 20  Replicate_Wild_Ignore_Table: 21 Last_Errno: 022  Last_Error: 23 Skip_Counter: 024 Exec_Master_Log_Pos: 227425 Relay_Log_Space: 194526  Until_Condition: None27  Until_Log_File: 28 Until_Log_Pos: 029  Master_SSL_Allowed: No30  Master_SSL_CA_File: 31  Master_SSL_CA_Path: 32  Master_SSL_Cert: 33  Master_SSL_Cipher: 34  Master_SSL_Key: 35 Seconds_Behind_Master: 036 Master_SSL_Verify_Server_Cert: No37 Last_IO_Errno: 038  Last_IO_Error: 39 Last_SQL_Errno: 040  Last_SQL_Error: 41  Replicate_Ignore_Server_Ids: 42 Master_Server_Id: 3306022043 Master_UUID: cda19536-5749-11e8-8c7a-000c29268dcd44  Master_Info_File: mysql.slave_master_info45 SQL_Delay: 046  SQL_Remaining_Delay: NULL47 Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates48 Master_Retry_Count: 8640049  Master_Bind: 50  Last_IO_Error_Timestamp: 51  Last_SQL_Error_Timestamp: 52  Master_SSL_Crl: 53  Master_SSL_Crlpath: 54  Retrieved_Gtid_Set: 55  Executed_Gtid_Set: 56 Auto_Position: 057  Replicate_Rewrite_DB: 58  Channel_Name: 59  Master_TLS_Version: 60 1 row in set (0.00 sec)61 62 ERROR: 63 No query specified64 65 root@db 14:56: [(none)]>