mariadb 主从复制
mariadb 主从复制
一、安装依赖包
yum install -y net-tools yum install -y wget # 切换aliyun的yum源 cd /etc/yum.repos.d/ mv CentOS-Base.repo CentOS-Base.repo.bak wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo # 重建源数据缓存 yum clean all yum makecache yum -y install vim-enhanced wget net-tools telnet vim lrzsz ntp yum -y install lshw pciutils gdisk system-storage-manager yum -y install bash-completion zip unzip bzip2 tree tmpwatch pinfo man-pages yum -y install nano vim-enhanced tmux screen yum -y install net-tools psmisclsof sysstat yum -y install yum-plugin-security yum-utils createrepo yum -y install get wget curl eliks lynx lftp mailx mutt reync yum -y install libaio make cmake gcc-c++ gcc zib zlib-devel open openssl-devel pcre pcre-devel
二、创建用户
useradd -M -r -s /bin/nologin mysql echo "mysql"|passwd --stdin mysql
三、下载安装
cd /opt/ wget http://mariadb.mirror.nucleus.be//mariadb-10.5.0/bintar-linux-systemd-x86_64/mariadb-10.5.0-linux-systemd-x86_64.tar.gz tar -zxvf mariadb-10.5.0-linux-systemd-x86_64.tar.gz -C /usr/local/ cd /usr/local/ ln -s mariadb-10.5.0-linux-systemd-x86_64/ mysql echo "PATH=/usr/local/mysql/bin:$PATH" > /etc/profile.d/mysql.sh
四、配置文件
cat >/etc/my.cnf<<"EOF" [client] port = 3306 socket = /data/mysql/data/mysql.sock default-character-set=utf8mb4 [mysql] prompt="\u@MariaDB0 \R:\m:\s [\d]> " no-auto-rehash default-character-set=utf8mb4 [mysqld] user = mysql port = 3306 extra_port=13306 socket = /data/mysql/data/mysql.sock basedir= /usr/local/mysql datadir= /data/mysql/data log-error=/data/mysql/logs/mysql-error.log pid-file=/data/mysql/data/mariadb.pid character-set-server = utf8mb4 skip_name_resolve = 1 open_files_limit = 65535 back_log = 1024 max_connections = 512 max_connect_errors = 1000000 table_open_cache = 1024 table_definition_cache = 1024 thread_stack = 512K external-locking = FALSE max_allowed_packet = 32M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 768 query_cache_size = 0 query_cache_type = 0 interactive_timeout = 600 wait_timeout = 600 tmp_table_size = 32M max_heap_table_size = 32M slow_query_log = 1 slow_query_log_file = /data/mysql/logs/slow.log long_query_time = 0.1 log_queries_not_using_indexes =1 min_examined_row_limit = 100 log_slow_admin_statements = 1 log_slow_slave_statements = 1 server-id = 3306 log-bin = /data/mysql/logs/mybinlog sync_binlog = 1 binlog_cache_size = 4M max_binlog_cache_size = 2G max_binlog_size = 1G expire_logs_days = 30 log_slave_updates binlog_format = row binlog_checksum = 1 relay_log_recovery = 1 relay-log-purge = 1 key_buffer_size = 32M read_buffer_size = 8M read_rnd_buffer_size = 4M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 lock_wait_timeout = 3600 innodb_thread_concurrency = 0 innodb_sync_spin_loops = 100 innodb_spin_wait_delay = 30 transaction_isolation = REPEATABLE-READ #innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 5734M innodb_buffer_pool_instances = 8 innodb_data_file_path = ibdata1:1G:autoextend innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 32M innodb_log_file_size = 2G innodb_log_files_in_group = 2 # 根据您的服务器IOPS能力适当调整 # 一般配普通SSD盘的话，可以调整到 10000 - 20000 # 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000 innodb_io_capacity = 8000 innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_purge_threads = 1 innodb_open_files = 65535 innodb_max_dirty_pages_pct = 50 innodb_flush_method = O_DIRECT #innodb_checksums = 1 #innodb_file_format = Barracuda #innodb_file_format_max = Barracuda innodb_lock_wait_timeout = 10 innodb_rollback_on_timeout = 1 innodb_print_all_deadlocks = 1 innodb_file_per_table = 1 innodb_stats_on_metadata = 0 symbolic-links=0 innodb_status_file = 1 #performance_schema performance_schema = 1 # 字符集设定utf8mb4 character-set-server=utf8mb4 collation-server=utf8mb4_unicode_ci init-connect='SET NAMES utf8mb4' # 优化 optimizer_switch = "mrr=on,mrr_cost_based=on,mrr_sort_keys=on" deadlock_search_depth_short = 3 deadlock_search_depth_long = 10 deadlock_timeout_long = 10000000 deadlock_timeout_short = 5000 slave-parallel-threads=8 # gtid gtid_strict_mode=1 wsrep_gtid_mode=1 [mysqld_safe] nice=-19 open-files-limit=65535 [mysqldump] quick max_allowed_packet = 64M EOF SERVIER_ID=`date +%S` sed -i "s/server-id = 3306/server-id = 3306"${SERVIER_ID}"/" /etc/my.cnf
五、修改权限
mkdir -p /data/mysql/{data,logs} chown mysql.mysql -R /data/mysql chown mysql.mysql -R /usr/local/mariadb-10.5.0-linux-systemd-x86_64 chown mysql.mysql -R /usr/local/mysql
六、初始化
# 初始化数据 cd /usr/local/mysql ./scripts/mysql_install_db --defaults-file=/etc/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data
七、启用关闭
# 启动mariadb ls /usr/local/mysql/support-files/systemd/ cp support-files/systemd/mariadb.service /lib/systemd/system/ systemctl daemon-reload systemctl start mariadb # 关闭maridb systemctl stop mariadb
八、登录
# 登录 [root@db136 mysql]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 5 Server version: 10.5.0-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@MariaDB0 10:36: [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.000 sec) root@MariaDB0 10:36: [(none)]> delete from mysql.user where user=''; root@MariaDB0 10:36: [(none)]> drop database test; # 创建管理员 grant all privileges on *.* to 'root'@'127.0.0.1' identified by 'root' with grant option; # 登录超级管理端口 mysql -h 127.0.0.1 -uroot -p -P13306
九、主从搭建
# 创建同步用户
# mariadb搭建主从 CREATE USER 'repl'@'%' identified by 'replpwd'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'
# 主库操作
# 主库上导出全备数据 [root@db136 opt]# mysqldump --all-databases --gtid=on --master-data=2 --single-transaction >full_backup.sql [root@db137 ~]# head -35 /opt/full_backup.sql |grep -Ei 'change|gtid_slave_pos' -- CHANGE MASTER TO MASTER_LOG_FILE='mybinlog.000007', MASTER_LOG_POS=11792; -- CHANGE MASTER TO MASTER_USE_GTID=slave_pos; -- SET GLOBAL gtid_slave_pos='0-330625-63'; [root@db137 ~]#
# 从库操作
# 在从库上操作 reset master ; SET GLOBAL gtid_slave_pos='0-330625-63'; CHANGE MASTER TO MASTER_HOST='192.168.142.136', MASTER_USER='repl', MASTER_PASSWORD='replpwd', MASTER_PORT=3306, MASTER_USE_GTID = slave_pos; start slave; show slave status\G;
十、模拟故障操作
#1、在从库上操作，然后在主库上操作，模拟同步故障 root@MariaDB0 15:38: [test]> set sql_log_bin=0; Query OK, 0 rows affected (0.000 sec) root@MariaDB0 15:39: [test]> delete from t1 where id = 1; Query OK, 1 row affected (0.002 sec) root@MariaDB0 15:40: [test]> select * from t1 limit 1; +----+---------+---------+------------+---------+--------+----------+--------+ | id | pay_min | pay_max | grade_type | subject | period | discount | price | +----+---------+---------+------------+---------+--------+----------+--------+ | 2 | 21 | 59 | 1 | 0 | 0 | 90 | 195.00 | +----+---------+---------+------------+---------+--------+----------+--------+ 1 row in set (0.000 sec) root@MariaDB0 15:43: [test]> set sql_log_bin=1; Query OK, 0 rows affected (0.000 sec)
# 主库 root@MariaDB0 15:35: [test]> delete from t1 where id=1; Query OK, 1 row affected (0.027 sec)
# 从库，查看同步状态 root@MariaDB0 15:45: [test]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.142.136 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mybinlog.000007 Read_Master_Log_Pos: 23722 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 12386 Relay_Master_Log_File: mybinlog.000007 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1032 Last_Error: Could not execute Delete_rows_v1 event on table test.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mybinlog.000007, end_log_pos 23691 Skip_Counter: 0 Exec_Master_Log_Pos: 23480 Relay_Log_Space: 12939 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1032 Last_SQL_Error: Could not execute Delete_rows_v1 event on table test.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mybinlog.000007, end_log_pos 23691 Replicate_Ignore_Server_Ids: Master_Server_Id: 330625 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: 0-330625-68 Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave_DDL_Groups: 4 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 1 1 row in set (0.000 sec) ERROR: No query specified root@MariaDB0 15:42: [test]>
1032错误的主要原因是主库更新或者是删除的记录在从库上不存在引起的。 处理此种错误一般有两种思路： 1、直接跳过错误执行语句 2、找到错误执行语句，修复从库数据 第一种解决方案会有造成主从不一致的隐患（delete语句可以跳过），第二种是从根本上解决问题比较推荐
/* 本案例不采用这种方式 -- 未启用gtid，跳过错误 stop slave; #表示跳过一步错误，后面的数字可变，（或者N条event，一条一条跳过） set global sql_slave_skip_counter =1; start slave; show slave status/G; # 还有一种方法跳过所有1032错误 # 更改my.cnf文件，在Replication settings下添加： # slave-skip-errors ＝ 1032 # 并重启数据库，然后start salve。 # 注意：因为要重启数据库，不推荐，除非错误事件太多。 */
# 从库具体操作 # 首先通过查看master的current_pos和当前slave的slave_pos确定需要从哪个gtid_slave_pos继续主从同步 select @@global.gtid_current_pos; # 主库操作 select @@global.gtid_slave_pos; # 从库操作 root@MariaDB0 16:15: [test]> select @@global.gtid_current_pos; # 主库操作 +---------------------------+ | @@global.gtid_current_pos | +---------------------------+ | 0-330625-75 | +---------------------------+ 1 row in set (0.000 sec) root@MariaDB0 16:10: [test]> select @@global.gtid_slave_pos; # 从库操作 +-------------------------+ | @@global.gtid_slave_pos | +-------------------------+ | 0-330625-73 | +-------------------------+ 1 row in set (0.000 sec)
/* 然后slave上执行 stop slave; //如果有多个slave复制通道（多主复制），需要全部都停 set global gtid_slave_pos="[new_slave_pos]"; start slave; show slave status\G; */
# 也解析主库对应的binlog，找点。然后向上找到最接近的:SET @@session.gtid_seq_no #/*!100001 SET @@session.gtid_seq_no=74*//*!*/; #BEGIN # /*!*/; # at 41136 # at 41186 #200101 16:26:10 server id 330625 end_log_pos 41186 CRC32 0x466842d0 Annotate_rows: #Q> delete from t3 where id <=5 #200101 16:26:10 server id 330625 end_log_pos 41240 CRC32 0x68a74c63 Table_map: `test`.`t3` mapped to number 40 # at 41240 #200101 16:26:10 server id 330625 end_log_pos 41443 CRC32 0x861e9804 Delete_rows: table id 40 flags: STMT_END_F ### DELETE FROM `test`.`t3` ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2=0 /* INT meta=0 nullable=0 is_null=0 */ ### @3=20 /* INT meta=0 nullable=0 is_null=0 */
stop slave; set global gtid_slave_pos='0-330625-74'; start slave; show slave status\G; # 参考资料：https://blog.51cto.com/arkling/2114962
# 一般使用slave_pos，当A->B，A挂掉，B当master，然后A好了，想要做B的slave情况下， #使用current_pos,因为B以前是主库，没有slave_pos这个值 现在是A挂掉了。B作为新的主库 stop slave ; reset slave all; 操作： SET GLOBAL gtid_slave_pos = ""; CHANGE MASTER TO MASTER_HOST='192.168.142.137', MASTER_USER='repl', MASTER_PASSWORD='replpwd', MASTER_PORT=3306, master_use_gtid=current_pos; start slave; show slave status\G;
