手工部署二进制mysql8.0.x及其升级测试
一、环境介绍
这里以部署8.0.19为例，后续以此升级到8.0.22
|
操作系统 |
主机名 |
IP |
角色 |
|
CentOS 7.6 |
db129 |
192.168.93.129 |
主库 |
|
CentOS 7.6 |
db130 |
192.168.93.130 |
从库1 |
|
CentOS 7.6 |
db131 |
192.168.93.131 |
从库2 |
二、该测试环境配置阿里云的yum源
|
cp /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.bak wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo cp /etc/yum.repos.d/epel.repo /etc/yum.repos.d/epel.repo.bak yum clean all yum makecache |
三、创建目录
mkdir -p /opt/mysql
四、安装系统依赖包
yum -y install make gcc-c++ cmake bison-devel ncurses-devel readline-devel libaio-devel perl libaio wget lrzsz vim libnuma* bzip2 xz tree
五、关闭selinux
sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config setenforce 0
六、修改系统限制参数
cat >> /etc/security/limits.conf <<"EOF" # ###custom # * soft nproc 20480 * hard nproc 65535 * hard nofile 1000000 * soft nofile 1000000 EOF
七、创建mysql用户
groupadd mysql
useradd -g mysql -s /sbin/nologin -d /usr/local/mysql -MN mysql
八、创建对应的目录
mkdir -p /data/mysql/mysql_3306/{data,logs,tmp}
九、修改文件描述符
sed -i 's/4096/unlimited/' /etc/security/limits.d/20-nproc.conf cat >>/etc/sysctl.conf <<"EOF" ################################################################ net.ipv4.tcp_keepalive_time =600 net.ipv4.tcp_syncookies = 1 net.ipv4.tcp_tw_reuse = 1 net.ipv4.tcp_timestamps=1 net.ipv4.tcp_tw_recycle=1 net.ipv4.tcp_fin_timeout = 30 net.ipv4.ip_local_port_range = 32768 60999 net.ipv4.tcp_max_syn_backlog = 1024 net.core.somaxconn = 1024 net.ipv4.tcp_max_tw_buckets = 5000 net.ipv4.tcp_syn_retries = 1 net.ipv4.tcp_synack_retries = 1 net.core.netdev_max_backlog = 1000 net.ipv4.tcp_max_orphans = 2000 #net.nf_conntrack_max = 25000000 #net.netfilter.nf_conntrack_max = 25000000 #net.netfilter.nf_conntrack_tcp_timeout_established = 180 #net.netfilter.nf_conntrack_tcp_timeout_time_wait = 120 #net.netfilter.nf_conntrack_tcp_timeout_close_wait = 60 #net.netfilter.nf_conntrack_tcp_timeout_fin_wait = 120 # 结合DDOS和TIME_WAIT过多，建议增加如下参数设置： # Use TCP syncookies when needed net.ipv4.tcp_syncookies = 1 net.ipv4.tcp_synack_retries=3 net.ipv4.tcp_syn_retries=3 net.ipv4.tcp_max_syn_backlog=2048 # Enable TCP window scaling # net.ipv4.tcp_window_scaling: = 1 # Increase TCP max buffer size net.core.rmem_max = 16777216 net.core.wmem_max = 16777216 # Increase Linux autotuning TCP buffer limits net.ipv4.tcp_rmem = 4096 87380 16777216 net.ipv4.tcp_wmem = 4096 65536 16777216 # Increase number of ports available net.ipv4.tcp_fin_timeout = 30 net.ipv4.tcp_keepalive_time = 300 net.ipv4.tcp_tw_reuse = 1 net.ipv4.tcp_tw_recycle = 1 net.ipv4.ip_local_port_range = 5000 65000 ################################################################ EOF sysctl -p
十、创建mysql的配置文件
说明：通常根据内存需要修改：innodb_buffer_pool_size = 1024M
一般是机器内存的50%~80%
cat >>/etc/bashrc<<"EOF" ulimit -u 65536 EOF source /etc/bashrc cp /etc/my.cnf /etc/my.cnf.bak rm -rf /etc/my.cnf cat >/data/mysql/mysql_3306/my.3306.cnf<<"EOF" [client] port = 3306 socket = /data/mysql/mysql_3306/mysql.sock [mysql] prompt="u@mysqldb R:m:s [d]> " no-auto-rehash innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G [mysqld] user = mysql port = 3306 basedir = /usr/local/mysql datadir = /data/mysql/mysql_3306/data socket = /data/mysql/mysql_3306/mysql.sock pid-file = mysql.3306.pid character-set-server = utf8mb4 skip_name_resolve = 1 lower_case_table_names = 1 innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G default_authentication_plugin=mysql_native_password #若你的MySQL数据库主要运行在境外，请务必根据实际情况调整本参数 default_time_zone = "+8:00" open_files_limit = 65535 back_log = 1024 max_connections = 2000 max_connect_errors = 1000000 table_open_cache = 1024 table_definition_cache = 1024 table_open_cache_instances = 64 thread_stack = 512K external-locking = FALSE max_allowed_packet = 32M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 3000 interactive_timeout = 600 wait_timeout = 600 tmp_table_size = 32M max_heap_table_size = 32M slow_query_log = 1 log_timestamps = SYSTEM slow_query_log_file = /data/mysql/mysql_3306/logs/slow.log log-error = /data/mysql/mysql_3306/logs/error.log long_query_time = 1 log_queries_not_using_indexes =1 log_throttle_queries_not_using_indexes = 60 min_examined_row_limit = 100 log_slow_admin_statements = 1 log_slow_slave_statements = 1 server-id = 3306 log-bin = /data/mysql/mysql_3306/logs/mysql-binlog sync_binlog = 1 binlog_cache_size = 4M max_binlog_cache_size = 2G max_binlog_size = 1G slave_preserve_commit_order = 1 slave_parallel_type = LOGICAL_CLOCK slave_parallel_workers = 4 log_bin_trust_function_creators = 1 binlog_rows_query_log_events = 1 #注意：MySQL 8.0开始，binlog_expire_logs_seconds选项也存在的话，会忽略expire_logs_days选项 binlog_expire_logs_seconds = 2592000 master_info_repository = TABLE relay_log_info_repository = TABLE gtid_mode = on enforce_gtid_consistency = 1 log_slave_updates = 1 #slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN' binlog_format = row binlog_checksum = 1 relay_log_recovery = 1 relay-log-purge = 1 relay_log=relay-bin 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 explicit_defaults_for_timestamp = 1 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 = 1024M innodb_buffer_pool_instances = 4 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 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 innodb_max_undo_log_size = 4G innodb_undo_directory = /data/mysql/mysql_3306/logs/undolog # 根据您的服务器IOPS能力适当调整 # 一般配普通SSD盘的话，可以调整到 10000 - 20000 # 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000 innodb_io_capacity = 10000 innodb_io_capacity_max = 20000 innodb_flush_sync = 0 innodb_flush_neighbors = 0 innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_purge_threads = 4 innodb_page_cleaners = 4 innodb_open_files = 65535 innodb_max_dirty_pages_pct = 50 innodb_flush_method = O_DIRECT innodb_lru_scan_depth = 4000 innodb_checksum_algorithm = crc32 innodb_lock_wait_timeout = 10 innodb_rollback_on_timeout = 1 innodb_print_all_deadlocks = 1 innodb_file_per_table = 1 innodb_online_alter_log_max_size = 4G innodb_stats_on_metadata = 0 innodb_undo_log_truncate = 1 #sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' # some var for MySQL 8 log_error_verbosity = 3 innodb_print_ddl_logs = 1 binlog_expire_logs_seconds = 604800 #innodb_dedicated_server = 0 innodb_status_file = 1 #注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快 innodb_status_output = 0 innodb_status_output_locks = 0 #performance_schema performance_schema = 1 performance_schema_instrument = '%memory%=on' performance_schema_instrument = '%lock%=on' #innodb monitor innodb_monitor_enable="module_innodb" innodb_monitor_enable="module_server" innodb_monitor_enable="module_dml" innodb_monitor_enable="module_ddl" innodb_monitor_enable="module_trx" innodb_monitor_enable="module_os" innodb_monitor_enable="module_purge" innodb_monitor_enable="module_log" innodb_monitor_enable="module_lock" innodb_monitor_enable="module_buffer" innodb_monitor_enable="module_index" innodb_monitor_enable="module_ibuf_system" innodb_monitor_enable="module_buffer_page" innodb_monitor_enable="module_adaptive_hash" [mysqldump] quick max_allowed_packet = 32M EOF
十一、解压安装包
cd /opt/mysql/ tar -xvJf mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz cd /usr/local ln -s /opt/mysql/mysql-8.0.19-linux-glibc2.12-x86_64 mysql chown -R mysql:mysql /usr/local/mysql chown -R mysql:mysql /data/mysql/ Server_id=1`date +%S%m` sed -i "s/server-id = 3306/server-id = "${Server_id}"/" /data/mysql/mysql_3306/my.3306.cnf grep server-id /data/mysql/mysql_3306/my.3306.cnf
十二、初始化数据
#/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3306/my.3306.cnf --basedir=/usr/local/mysql --datadir=/data/mysql/mysql_3306/data --initialize-insecure /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3306/my.3306.cnf --initialize-insecure # 另外开启一个窗口，查看日志 tail -f /data/mysql/mysql_3306/logs/error.log
十三、推荐的启动方式
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3306/my.3306.cnf & echo "export PATH=$PATH:/usr/local/mysql/bin" >>/etc/profile source /etc/profile
十四、准备root密码文件
cat >/opt/mysql/init_account.sql<<"EOF" ALTER user 'root'@'localhost' IDENTIFIED BY 'v3FS7nlfSv5M8ORYG2bS'; flush privileges; EOF
cat >/root/.mysql.root.cnf <<"EOF" [client] user=root password="v3FS7nlfSv5M8ORYG2bS" socket=/data/mysql/mysql_3306/mysql.sock EOF mysql -S /data/mysql/mysql_3306/mysql.sock -e "show databases" mysql -S /data/mysql/mysql_3306/mysql.sock < /opt/mysql/init_account.sql
十五、设置快捷登录方式
cat >>~/.bashrc<<"EOF" alias mysql.root="/usr/local/mysql/bin/mysql --defaults-extra-file=/root/.mysql.root.cnf" alias mysql.start="/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3306/my.3306.cnf &" alias mysql.stop="/usr/local/mysql/bin/mysqladmin --defaults-file=/root/.mysql.root.cnf shutdown" alias mysql.restart="sh /opt/mysql/mysql.restart" EOF cat >/opt/mysql/mysql.restart<<"EOF" #/bin/bash is_start_stop=`ps -ef|grep mysqld_safe|grep -v 'grep' |wc -l` if [ ${is_start_stop} -eq 1 ]; then /usr/local/mysql/bin/mysqladmin --defaults-file=/root/.mysql.root.cnf shutdown /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3306/my.3306.cnf & else /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3306/my.3306.cnf & fi EOF source ~/.bashrc
十六、登录重启等
[root@db131 local]# mysql.root Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 15 Server version: 8.0.19 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> exit Bye [root@db131 local]# mysql.stop 2021-01-12T07:38:24.619281Z mysqld_safe mysqld from pid file /data/mysql/mysql_3306/data/mysql.3306.pid ended [1]+ Done /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3306/my.3306.cnf [root@db131 local]# mysql.start [1] 27858 [root@db131 local]# 2021-01-12T07:38:52.676289Z mysqld_safe Logging to '/data/mysql/mysql_3306/logs/error.log'. 2021-01-12T07:38:52.715254Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/mysql_3306/data
十七、事先准备测试数据
[root@db129 local]# mysql.root -e "create database testdb" [root@db129 local]# mysql.root testdb <test.sql # 根据一张大表，生成多个数据表 [root@db129 local]# cat 1.sh #/bin/bash for i in {1..100}; do /usr/local/mysql/bin/mysql --defaults-extra-file=/root/.mysql.root.cnf -e "use testdb; drop table if exists user"${i}" ; create table user"${i}" like users;" /usr/local/mysql/bin/mysql --defaults-extra-file=/root/.mysql.root.cnf -e "use testdb; insert into user"${i}" (organization_id, organization_name, short_name, business_licence, established_time, effective_begin_time, effective_end_time, legal_person, legal_person_id_card, school_history, employee_number, student_number, student_number_per_year, company_url, register_address, industry_position, hierachical_indentification, contract_number, settle_period, business_linkman, business_linkman_phone, finance_linkman, finance_linkman_phone, bd_linkman, bd_linkman_phone, bd_op_linkman, bd_op_linkman_phone, state, state_op_remarks, service_charge_type, product_type, create_date, update_date, create_user_id, update_user_id, del_flag, remarks, create_user_name, update_user_name, bank_no, payment_mode, service_charge_rate, charge_taken_mode, credit_amount, month_credit_amount, capital_no, seals_code, seals_key, org_out, org_out_days, org_out_remarks, guarantee_cash, phone_check_parent, guarantee_cash_lee_than, settlement_cycle, guarantee_cash_type, guarantee_cash_prop, guarantee_taken_mode, bond_begin_date) select organization_id, organization_name, short_name, business_licence, established_time, effective_begin_time, effective_end_time, legal_person, legal_person_id_card, school_history, employee_number, student_number, student_number_per_year, company_url, register_address, industry_position, hierachical_indentification, contract_number, settle_period, business_linkman, business_linkman_phone, finance_linkman, finance_linkman_phone, bd_linkman, bd_linkman_phone, bd_op_linkman, bd_op_linkman_phone, state, state_op_remarks, service_charge_type, product_type, create_date, update_date, create_user_id, update_user_id, del_flag, remarks, create_user_name, update_user_name, bank_no, payment_mode, service_charge_rate, charge_taken_mode, credit_amount, month_credit_amount, capital_no, seals_code, seals_key, org_out, org_out_days, org_out_remarks, guarantee_cash, phone_check_parent, guarantee_cash_lee_than, settlement_cycle, guarantee_cash_type, guarantee_cash_prop, guarantee_taken_mode, bond_begin_date from users;" done [root@db129 local]#
十八、基于gtid部署一主两从
# 创建复制账号 create user 'repl'@'192.168.93.%' IDENTIFIED BY 'repl123'; GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl'@'192.168.93.%'; # 在主库上导出数据，推荐全部导出，尽可能保持一致 mysqldump --defaults-extra-file=/root/.mysql.root.cnf --single-transaction --master-data=2 --set-gtid-purged=on --triggers --routines --events --all-databases>/root/all_databases.dump.sql & scp /root/all_databases.dump.sql root@192.168.93.130:/root/ scp /root/all_databases.dump.sql root@192.168.93.131:/root/ # 在每个从库中分别执行导入操作 mysql.root <all_databases.dump.sql &
十九、部署同步服务
db130部署同步服务
# db130 [root@db130 ~]# head -32 all_databases.dump.sql|tail -8 SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ 'c994129d-54a7-11eb-9b50-000c29a3ed2d:1-74672'; -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-binlog.000009', MASTER_LOG_POS=990436931; [root@db130 ~]# [root@db130 ~]# mysql.root Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 12 Server version: 8.0.19 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> reset master; Query OK, 0 rows affected (0.09 sec) mysql> reset slave all; Query OK, 0 rows affected (0.22 sec) mysql> SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ 'c994129d-54a7-11eb-9b50-000c29a3ed2d:1-74672'; mysql>CHANGE MASTER TO MASTER_HOST='192.168.93.129', MASTER_USER='repl', MASTER_PASSWORD='repl123', MASTER_PORT=3306, MASTER_AUTO_POSITION = 1; start slave; show slave statusG; mysql> show slave statusG; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.93.129 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-binlog.000009 Read_Master_Log_Pos: 990436931 Relay_Log_File: db130-relay-bin.000002 Relay_Log_Pos: 425 Relay_Master_Log_File: mysql-binlog.000009 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: 990436931 Relay_Log_Space: 633 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: 14901 Master_UUID: c994129d-54a7-11eb-9b50-000c29a3ed2d Master_Info_File: mysql.slave_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: c994129d-54a7-11eb-9b50-000c29a3ed2d:1-74672 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set (0.01 sec) ERROR: No query specified mysql>
db131部署同步服务
# db131 [root@db131 ~]# head -32 all_databases.dump.sql|tail -8 SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ 'c994129d-54a7-11eb-9b50-000c29a3ed2d:1-74672'; -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-binlog.000009', MASTER_LOG_POS=990436931; [root@db131 ~]# [root@db131 ~]# mysql.root Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 11 Server version: 8.0.19 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> reset master; Query OK, 0 rows affected (0.14 sec) mysql> reset slave; Query OK, 0 rows affected (0.03 sec) mysql> mysql> show slave statusG; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.93.129 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-binlog.000009 Read_Master_Log_Pos: 990436931 Relay_Log_File: db131-relay-bin.000002 Relay_Log_Pos: 425 Relay_Master_Log_File: mysql-binlog.000009 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: 990436931 Relay_Log_Space: 633 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: 14901 Master_UUID: c994129d-54a7-11eb-9b50-000c29a3ed2d Master_Info_File: mysql.slave_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: c994129d-54a7-11eb-9b50-000c29a3ed2d:1-74672 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set (0.00 sec) ERROR: No query specified mysql>
二十、模拟增量数据，验证同步服务
# 主库当前状态 [root@db129 local]# mysql.root -e "show master status" +---------------------+-----------+--------------+------------------+----------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------------+-----------+--------------+------------------+----------------------------------------------+ | mysql-binlog.000009 | 990436931 | | | c994129d-54a7-11eb-9b50-000c29a3ed2d:1-74672 | +---------------------+-----------+--------------+------------------+----------------------------------------------+ [root@db129 local]# sh /usr/local/3.sh & [3] 110976 [root@db129 local]#
#从库db130 [root@db130 ~]# mysql.root -e "show slave statusG" *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.93.129 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-binlog.000009 Read_Master_Log_Pos: 991607336 Relay_Log_File: db130-relay-bin.000002 Relay_Log_Pos: 1170830 Relay_Master_Log_File: mysql-binlog.000009 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: 991607336 Relay_Log_Space: 1171038 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: 14901 Master_UUID: c994129d-54a7-11eb-9b50-000c29a3ed2d Master_Info_File: mysql.slave_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: c994129d-54a7-11eb-9b50-000c29a3ed2d:74673-74681 Executed_Gtid_Set: c994129d-54a7-11eb-9b50-000c29a3ed2d:1-74681 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: [root@db130 ~]#
#从库db131 [root@db131 ~]# mysql.root -e "show slave statusG" *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.93.129 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-binlog.000009 Read_Master_Log_Pos: 991607336 Relay_Log_File: db131-relay-bin.000002 Relay_Log_Pos: 1170830 Relay_Master_Log_File: mysql-binlog.000009 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: 991607336 Relay_Log_Space: 1171038 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: 14901 Master_UUID: c994129d-54a7-11eb-9b50-000c29a3ed2d Master_Info_File: mysql.slave_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: c994129d-54a7-11eb-9b50-000c29a3ed2d:74673-74681 Executed_Gtid_Set: c994129d-54a7-11eb-9b50-000c29a3ed2d:1-74681 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: [root@db131 ~]#
至此，实验环境部署完毕。
二十一、先升级从库
#这里先升级db131这个从库，在此期间，主库和db130可以正常使用，在主库模拟其他增量操作。
步骤如下：
1、升级db131
1.1先停止db131的同步服务
[root@db131 ~]# mysql.root -e "stop slave"
1.2、停止mysqld服务
[root@db131 ~]# mysql.stop 2021-01-13T02:29:52.708073Z mysqld_safe mysqld from pid file /data/mysql/mysql_3306/data/mysql.3306.pid ended [root@db131 ~]#
1.3、备份数据目录(可选，如果磁盘空间允许，建议操作)
[root@db131 local]# cd /data/mysql/ [root@db131 mysql]# zip -r mysql_3306.zip mysql_3306/ [root@db131 mysql]# du -sh * 16G mysql_3306 4.6G mysql_3306.zip [root@db131 mysql]#
1.4、删除软连接
[root@db131 mysql]# cd /usr/local/ [root@db131 local]# unlink mysql
1.5、解压新版安装包
[root@db131 local]# cd /opt/mysql/ [root@db131 mysql]# # 解压 [root@db131 mysql]# tar -xvJf mysql-8.0.22-linux-glibc2.12-x86_64.tar.xz [root@db131 mysql]# cd /usr/local/ [root@db131 local]# ln -s /opt/mysql/mysql-8.0.22-linux-glibc2.12-x86_64 mysql [root@db131 local]# chown -R mysql:mysql /usr/local/mysql [root@db131 local]# chown -R mysql:mysql /data/mysql/
1.6、启动mysql服务
[root@db131 local]# mysql.start [1] 35193 [root@db131 local]# 2021-01-13T03:13:33.716563Z mysqld_safe Logging to '/data/mysql/mysql_3306/logs/error.log'. 2021-01-13T03:13:33.755643Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/mysql_3306/data [root@db131 local]# # 启动完毕后，登录查看 [root@db131 local]# mysql.root -e "select version()" +-----------+ | version() | +-----------+ | 8.0.22 | +-----------+ [root@db131 local]#
#这里强调一下，必须再次重启一次，使之生效 # 从8.0.16开始，变得如此简单，不在需要mysql_upgrade方式来升级了 [root@db131 local]# mysql.stop 2021-01-13T03:16:40.995849Z mysqld_safe mysqld from pid file /data/mysql/mysql_3306/data/mysql.3306.pid ended [1]+ Done /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3306/my.3306.cnf [root@db131 local]# mysql.start [1] 36872 [root@db131 local]# 2021-01-13T03:16:47.452604Z mysqld_safe Logging to '/data/mysql/mysql_3306/logs/error.log'. 2021-01-13T03:16:47.484759Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/mysql_3306/data [root@db131 local]# [root@db131 local]# mysql.root -e "select version()" +-----------+ | version() | +-----------+ | 8.0.22 | +-----------+
1.7、查看同步状态
[root@db131 local]# mysql.root -e "show slave status G" *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.93.129 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-binlog.000009 Read_Master_Log_Pos: 993948146 Relay_Log_File: db131-relay-bin.000006 Relay_Log_Pos: 426 Relay_Master_Log_File: mysql-binlog.000009 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: 993948146 Relay_Log_Space: 635 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: 14901 Master_UUID: c994129d-54a7-11eb-9b50-000c29a3ed2d Master_Info_File: mysql.slave_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: c994129d-54a7-11eb-9b50-000c29a3ed2d:1-74699 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: [root@db131 local]#
2、升级db130
2.1、先停止db130的同步服务
[root@db130 ~]# mysql.root -e "stop slave"
2.2、停止mysqld服务
[root@db130 ~]# mysql.stop [root@db130 ~]# 2021-01-13T03:22:04.936102Z mysqld_safe mysqld from pid file /data/mysql/mysql_3306/data/mysql.3306.pid ended
2.3、备份数据目录(可选，如果磁盘空间允许，建议操作)
[root@db130 ~]# cd /data/mysql/ [root@db130 mysql]# tar -czf mysql_3306.tar.gz mysql_3306/ &
2.4、删除软连接
[root@db130 mysql]# cd /usr/local/ [root@db130 local]# unlink mysql
2.5、解压新版安装包
[root@db130 local]# cd /opt/mysql/ [root@db130 mysql]# # 解压 [root@db130 mysql]# tar -xvJf mysql-8.0.22-linux-glibc2.12-x86_64.tar.xz [root@db130 mysql]# cd /usr/local/ [root@db130 local]# ln -s /opt/mysql/mysql-8.0.22-linux-glibc2.12-x86_64 mysql [root@db130 local]# chown -R mysql:mysql /usr/local/mysql [root@db130 local]# chown -R mysql:mysql /data/mysql/
2.6、启动mysql服务
[root@db130 local]# mysql.start [1] 32357 [root@db130 local]# 2021-01-13T09:34:26.845907Z mysqld_safe Logging to '/data/mysql/mysql_3306/logs/error.log'. 2021-01-13T09:34:26.957959Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/mysql_3306/data [root@db130 local]# # 启动完毕后，登录查看 [root@db130 local]# mysql.root -e "select version()" +-----------+ | version() | +-----------+ | 8.0.22 | +-----------+ [root@db130 local]#
#这里强调一下，必须再次重启一次，使之生效 # 从8.0.16开始，变得如此简单，不在需要mysql_upgrade方式来升级了 [root@db130 local]# mysql.stop [root@db130 local]# 2021-01-13T09:37:03.387329Z mysqld_safe mysqld from pid file /data/mysql/mysql_3306/data/mysql.3306.pid ended [1]+ Done /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3306/my.3306.cnf [root@db130 local]# mysql.start [1] 34037 [root@db130 local]# 2021-01-13T09:37:13.664956Z mysqld_safe Logging to '/data/mysql/mysql_3306/logs/error.log'. 2021-01-13T09:37:13.703788Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/mysql_3306/data [root@db130 local]# mysql.root -e "select version()" +-----------+ | version() | +-----------+ | 8.0.22 | +-----------+ [root@db130 local]#
3、升级主库db129
注意，为了安全期间，先停止所有从库的同步服务。
[root@db130 local]# mysql.root -e "stop slave" [root@db131 local]# mysql.root -e "stop slave"
3.1、设置只读状态
mysql> set global read_only=0; Query OK, 0 rows affected (0.12 sec)
3.2、停止服务
[root@db129 ~]# mysql.stop
3.3、备份数据
[root@db129 ~]# cd /data/mysql/ [root@db129 mysql]# zip -r mysql_3306.zip mysql_3306/ [root@db129 mysql]# du -sh * 25G mysql_3306 7.7G mysql_3306.zip [root@db129 mysql]#
3.4、删除软连接
[root@db129 mysql]# cd /usr/local/ [root@db129 local]# unlink mysql
3.5、解压安装包
[root@db129 local]# cd /opt/mysql/ [root@db129 mysql]# tar -xvJf mysql-8.0.22-linux-glibc2.12-x86_64.tar.xz [root@db130 ~]# cd /usr/local/ [root@db130 local]# ln -s /opt/mysql/mysql-8.0.22-linux-glibc2.12-x86_64 mysql [root@db130 local]# chown -R mysql:mysql /usr/local/mysql [root@db130 local]# chown -R mysql:mysql /data/mysql/
3.6、启动mysql服务
[root@db129 local]# mysql.start [1] 112050 [root@db129 local]# 2021-01-13T10:47:59.274383Z mysqld_safe Logging to '/data/mysql/mysql_3306/logs/error.log'. 2021-01-13T10:47:59.390555Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/mysql_3306/data [root@db129 local]# # 启动完毕后，登录查看 [root@db129 local]# mysql.root -e "select version()" +-----------+ | version() | +-----------+ | 8.0.22 | +-----------+ [root@db129 local]#
#这里强调一下，必须再次重启一次，使之生效 # 从8.0.16开始，变得如此简单，不在需要mysql_upgrade方式来升级了 [root@db129 local]# mysql.stop 2021-01-13T10:49:43.018402Z mysqld_safe mysqld from pid file /data/mysql/mysql_3306/data/mysql.3306.pid ended [1]+ Done /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3306/my.3306.cnf [root@db129 local]# [root@db129 local]# mysql.start [1] 113723 [root@db129 local]# 2021-01-13T10:49:48.554893Z mysqld_safe Logging to '/data/mysql/mysql_3306/logs/error.log'. 2021-01-13T10:49:48.588319Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/mysql_3306/data [root@db129 local]# mysql.root -e "select version()" +-----------+ | version() | +-----------+ | 8.0.22 | +-----------+ [root@db129 local]#
3.7、启动从库的同步服务
# 启动db130同步服务 [root@db130 local]# mysql.root -e "start slave" [root@db130 local]# mysql.root -e "show slave statusG" *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.93.129 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-binlog.000011 Read_Master_Log_Pos: 196 Relay_Log_File: db130-relay-bin.000007 Relay_Log_Pos: 377 Relay_Master_Log_File: mysql-binlog.000011 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: 196 Relay_Log_Space: 856 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: 14901 Master_UUID: c994129d-54a7-11eb-9b50-000c29a3ed2d Master_Info_File: mysql.slave_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: c994129d-54a7-11eb-9b50-000c29a3ed2d:1-74699 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: [root@db130 local]#
#启动db131同步服务 [root@db131 ~]# mysql.root -e "start slave" [root@db131 ~]# mysql.root -e "show slave statusG" *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.93.129 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-binlog.000011 Read_Master_Log_Pos: 196 Relay_Log_File: db131-relay-bin.000007 Relay_Log_Pos: 377 Relay_Master_Log_File: mysql-binlog.000011 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: 196 Relay_Log_Space: 856 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: 14901 Master_UUID: c994129d-54a7-11eb-9b50-000c29a3ed2d Master_Info_File: mysql.slave_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: c994129d-54a7-11eb-9b50-000c29a3ed2d:1-74699 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: [root@db131 ~]#
附录：
1、centos永久修改主机名
# centos7修改主机名的方法 hostnamectl set-hostname db129 hostnamectl set-hostname db130 hostnamectl set-hostname db131
2、修改主机对应关系
cat >>/etc/hosts <<"EOF" 192.168.93.129 db129 192.168.93.130 db130 192.168.93.131 db131 EOF
3、关闭防火墙和iptables
systemctl disable --now firewalld systemctl disable --now dnsmasq systemctl disable --now NetworkManager systemctl disable --now iptables setenforce 0 sed -i 's#SELINUX=enforcing#SELINUX=disabled#g' /etc/sysconfig/selinux sed -i 's#SELINUX=enforcing#SELINUX=disabled#g' /etc/selinux/config systemctl stop firewalld.service systemctl stop iptables.service echo "systemctl stop iptables.service" >>/etc/rc.local echo "systemctl stop firewalld.service" >>/etc/rc.local chmod +x /etc/rc.d/rc.local
4、阿里云yum配置
1、禁用 yum插件 fastestmirror 1.1、修改插件的配置文件 cp /etc/yum/pluginconf.d/fastestmirror.conf /etc/yum/pluginconf.d/fastestmirror.conf.bak vi /etc/yum/pluginconf.d/fastestmirror.conf enabled = 1 //由1改为0，禁用该插件 1.2、修改yum的配置文件 cp /etc/yum.conf /etc/yum.conf.bak vi /etc/yum.conf plugins=1 //改为0，不使用插件 2. 获取阿里云 repo cp /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.bak wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo cp /etc/yum.repos.d/epel.repo /etc/yum.repos.d/epel.repo.bak wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo 3.清理原来的缓存，重新缓存 yum clean all yum makecache