MySQL 双主热备部署

于 2022-01-06 15:32:20 发布
阅读量181 收藏
点赞数
CC 4.0 BY-SA版权
文章标签： mysql 数据库 database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wjplj/article/details/122344609
本文档详细介绍了如何在Linux环境中创建MySQL用户、解压部署MySQL，配置双主同步以及通过Keepalived实现高可用。包括初始化数据库、设置权限、配置复制以及Keepalived的VRRP脚本等关键步骤。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

创建用户

groupadd mysql
useradd -r -g mysql -s /sbin/nologin mysql

 解压并部署MySQL

tar xvf mysql-8.0.22-linux-glibc2.12-x86_64.tar.xz
mv mysql-8.0.22-linux-glibc2.12-x86_64 /data/mysql-8.0.22

创建部署路径并授权

mkdir /app
chown secure.secure -R /app

1、vim /etc/my.cnf

[client]
port    = 3306
socket  = /data/mysql_data/mysql.sock
​
[mysql]
prompt="\u@amfe-srv-1 \R:\m:\s [\d]> "
no-auto-rehash
default-character-set=utf8mb4
​
​
[mysqld]
user    = mysql
port    = 3306
basedir = /data/mysql-8.0.22
datadir = /data/mysql_data
socket  = /data/mysql_data/mysql.sock
pid-file = /data/mysql_data/mysql.pid
default_authentication_plugin=mysql_native_password
character-set-server = utf8mb4
​
open_files_limit    = 65535
back_log = 1024
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
sort_buffer_size = 1024M
join_buffer_size = 1024M
thread_cache_size = 64
interactive_timeout = 28800
wait_timeout = 28800
tmp_table_size = 32M
max_heap_table_size = 32M
log_timestamps = SYSTEM
​
slow_query_log_file = /data/mysql_data/slow.log
log-error = /data/mysql_data/error.log
long_query_time = 2
slow_query_log = 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
# 另一台为2
server-id = 1   
​
log-bin = mysql-bin
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 200M
max_binlog_size = 500M
binlog_format = ROW
binlog_checksum = none
expire_logs_days = 15
​
master_info_repository = TABLE
relay_log_info_repository = TABLE
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
​
relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 1024M
read_buffer_size = 1024M
read_rnd_buffer_size = 1024M
bulk_insert_buffer_size = 1024M
#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 = 32
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30
​
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 16
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:100M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_files_in_group = 4
innodb_max_undo_log_size = 100M
innodb_undo_directory = /data/mysql_data/undo
innodb_undo_tablespaces = 20
innodb_file_per_table=1
innodb_log_file_size = 512M
innodb_strict_mode = 0
# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 7500
innodb_io_capacity_max = 15000
innodb_flush_sync = 0
innodb_flush_neighbors = 0
innodb_write_io_threads = 1000
innodb_read_io_threads = 1000
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 90
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
innodb_lock_wait_timeout = 120
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_online_alter_log_max_size = 4G
innodb_stats_on_metadata = 0
innodb_undo_log_truncate = 1
innodb_status_file = 1
innodb_file_per_table=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"
​
auto-increment-increment = 2
auto-increment-offset = 1
slave-skip-errors = all
skip-name-resolve
skip-external-locking
default-time_zone = '+8:00'
lower_case_table_names=1
max_connections=3000
max_allowed_packet=524288000
net_read_timeout = 120
net_write_timeout = 120
​
secure-file-priv = /data/backupDB/dump
 

1、 普通用户授权
 

Root下执行
 

[root@localhost mysql]# chmod 755 -R /app/mysql/
 

2、 初始化数据库
 

Mysql路径自定
my.cnf文件路径自定
初始化

 

./bin/mysqld --defaults-file=/app/mysql/conf/my.cnf --user=secure --initialize
 

如果出现异常需要把data目录下内容全部删除，重新初始化
Log目录下error.log内容中有初始化生成的数据库密码，记得记录下来，登陆数据库要使用
root@localhost: .k9lZfeK&6P=  #初始化密码
 

3、 启动
 

mysqld_safe --defaults-file=/app/mysql/conf/my.cnf --user=secure &
 

4、 改密、创建、授权
 

ALTER USER 'root'@'localhost' IDENTIFIED BY 'zkpass!@#';
create user 'root'@'%' identified by 'zkpass!@#';
grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;
 

双主同步
 

Master 1**操作**
 

mysql> grant replication slave,replication client on *.* to repl@'172.16.10.%' identified by 'zkgl!@#';
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
锁表，待同步配置完成在解锁
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
| mysql-bin.000001 |   1450 |       |         |          |
 

Master 2**操作**
 

mysql> grant replication slave,replication client on *.* to repl@'172.16.10.%' identified by 'zkgl!@#';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
| mysql-bin.000002 |   1450 |       |         |          |
1 row in set (0.00 sec)
 

Master 1**操作**
 

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
查看当前的binlog以及数据所在位置
mysql> show master status;+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 |      996 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
开启同步B机器
change master to master_host='172.16.10.205', master_user='repl',master_password='zkgl!@#',master_log_file='mysql-bin.000001',master_log_pos=150;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status \G
•    Slave_IO_Running: Yes
•    Slave_SQL_Running: Yes
 

Master 2**操作**
 

查看 master情况
 

mysql> show master status;+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      150 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)
mysql> unlock tables;
Query OK, 0 rows affected (0.01 sec)
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
开启同步A机器
change master to master_host='172.16.10.204', master_user='repl',master_password='zkgl!@#',master_log_file='mysql-bin.000001',master_log_pos=150;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G
•    Slave_IO_Running: Yes
•    Slave_SQL_Running: Yes
 

5、 安装keeplived并配置
 

设置防火墙开启keepliced广播地址
firewall-cmd --direct --permanent --add-rule ipv4 filter INPUT 0 --destination 224.0.0.18 --protocol vrrp -j ACCEPT
firewall-cmd --direct --permanent --add-rule ipv4 filter OUTPUT 0 --destination 224.0.0.18 --protocol vrrp -j ACCEPT
firewall-cmd --reload
 

Master 1操作
 

global_defs {    
•   notification_email {      
•       acassen@firewall.loc      
•       failover@firewall.loc      
•       sysadmin@firewall.loc    
    }    
    notification_email_from Alexandre.Cassen@firewall.loc    
    smtp_server 127.0.0.1    
    smtp_connect_timeout 30    
    enable_script_security    
    router_id LVS_DEVEL 
} 
vrrp_script chk_mysql {     
    script "/usr/local/src/chk_mysql.sh"     
    interval 2     
    weight -5    
    # fall 3    
    # rise 2     
    user root 
} 
vrrp_instance SERVER_VI {     
    state MASTER     
    interface eth0     
    virtual_router_id 51     
    priority 110     
    advert_int 1 
    authentication {        
        auth_type PASS         
        auth_pass 1111     
    }     
    track_script {         
        chk_mysql 
    }      
    virtual_ipaddress {         
        172.16.10.207     
    } 
} 
 

 

Master 2操作
 

global_defs {
    notification_email {      
        acassen@firewall.loc      
        failover@firewall.loc      
        sysadmin@firewall.loc    
    }    
    notification_email_from Alexandre.Cassen@firewall.loc    
    smtp_server 127.0.0.1    
    smtp_connect_timeout 30    
    enable_script_security    
    router_id LVS_DEVEL 
} 
vrrp_script chk_mysql {     
    script "/usr/local/src/chk_mysql.sh"     
    interval 2    
    weight -5    
    # fall 3    
    # rise 2     
    user root 
} 
vrrp_instance SERVER_VI {     
    state BACKUP     
    interface eth0     
    virtual_router_id 51     
    priority 100     
    advert_int 1 
    authentication {        
        auth_type PASS         
        auth_pass 1111     
    }    
    track_script {         
        chk_mysql 
    }      
    virtual_ipaddress {         
        172.16.10.207     
    } 
}
 

编写chk_mysql.sh 脚本
 

#!/bin/bash  
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)  
if [ "${counter}" -eq 0 ]; then     
    systemctl stop keepalived 
fi
 


                

        

    

  



    

      

        

            

              
                
                  wjplj
                
              
            

            

                关注
              
            

        

        

          
      

      










            

  



          




        



    





    



      

      

        
      

      





	

		评论	
	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值