Linux安装MYSQL并部署主从复制集群

主节点部署

• 安装数据库

  • Ubuntu

  apt-get install mysql-server -y
  systemctl start mysql
  systemctl enabled mysql
  

  • CentOS

  参考CentOS 7.4通过rpm包离线安装 Mysql8.0

• 配置数据库

  • 修改 mysql 配置文件

    vim /etc/mysql/mysql.cnf
    
    [client]
    default-character-set=utf8mb4
    
    [mysql]
    default-character-set=utf8mb4
    
    [mysqld]
    server-id=1
    log_bin= /var/log/mysql/mysql-bin.log
    character-set-server=utf8mb4
    expire_logs_days=14
    max_binlog_size=100M
    
    # Logging
    log_warnings  = 2
    slow_query_log= 1
    slow_query_log_file = /var/log/mysql/slow.log
    long_query_time = 0.5
    log_slow_rate_limit = 1000
    log_slow_verbosity = query_plan
    log-queries-not-using-indexes
    log_slow_admin_statements
    

  • 创建数据同步用户并授权

    grant replication slave on *.* to repluser@'slave-ip' identified by 'replpass';
    

  • 重启数据库

    systemctl restart mysql
    

  • 登录数据库确认

    mysql> show variables like '%character%';
    +--------------------------+----------------------------+
    | Variable_name            | Value                      |
    +--------------------------+----------------------------+
    | character_set_client     | utf8mb4                    |
    | character_set_connection | utf8mb4                    |
    | character_set_database   | utf8mb4                    |
    | character_set_filesystem | binary                     |
    | character_set_results    | utf8mb4                    |
    | character_set_server     | utf8mb4                    |
    | character_set_system     | utf8                       |
    | character_sets_dir       | /usr/share/mysql/charsets/ |
    +--------------------------+----------------------------+
    mysql> show master status;
    +-----------------+----------+-------------+-----------------+------------------+
    | File            | Position |Binlog_Do_DB |Binlog_Ignore_DB |Executed_Gtid_Set |
    +-----------------+----------+-------------+-----------------+------------------+
    | mysql-bin.000001|      154 |             |                 |                  |
    +-----------------+----------+-------------+-----------------+------------------+
    

从节点部署

• 安装数据库

  • Ubuntu

  apt-get install mysql-server -y
  systemctl start mysql
  systemctl enabled mysql
  

  • CentOS

  参考CentOS 7.4通过rpm包离线安装 Mysql8.0

• 配置数据库

  • 修改 mysql 配置文件

    vim /etc/mysql/mysql.cnf
    
    [client]
    default-character-set=utf8mb4
    
    [mysql]
    default-character-set=utf8mb4
    
    [mysqld]
    server-id= 2
    character-set-server=utf8mb4
    expire_logs_days=14
    max_binlog_size=100M
    
    # Logging
    log_warnings  = 2
    slow_query_log= 1
    slow_query_log_file = /var/log/mysql/slow.log
    long_query_time = 0.5
    log_slow_rate_limit = 1000
    log_slow_verbosity = query_plan
    log-queries-not-using-indexes
    log_slow_admin_statements
    

  • 重启数据库

    systemctl restart mysql
    

  • 登录数据库配置主从连接

    mysql> change master to master_host='master-ip',master_port=3306,master_user='repluser',master_password='replpass',master_log_file='mysql-bin.000001',master_log_pos=154;
    

  • 启动同步，确定状态

    mysql> start slave;
    mysql> show slave status\G;  
    
    													...
                 Slave_IO_Running: Yes  
                Slave_SQL_Running: Yes  
    													... 
    

• 创建数据库

  • 在主节点创建数据库及对应用户

    • 创建 region 及 console库

      mysql> create database region;
      mysql> create database console;
      mysql> grant all privileges on *.* to rainbond@"*" identified by 'Rainbond';
      

  • 在从节点确认数据已经同步

    • 检测数据库是否已经同步

      mysql> show databases;
      +--------------------+
      | Database           |
      +--------------------+
      | information_schema |
      | console            |
      | mysql              |
      | performance_schema |
      | region             |
      | sys                |
      +--------------------+
      

    • 检测用户是否已经同步

      mysql> select Host,User from user;
      +-----------+------------------+
      | Host      | User             |
      +-----------+------------------+
      | %         | rainbond      |
      | localhost | debian-sys-maint |
      | localhost | mysql.session    |
      | localhost | mysql.sys        |
      | localhost | root             |
      +-----------+------------------+
      

• 从节点配置备份计划

• 安装并配置计划任务

apt-get install cron
crontab -e
0 3 * * * /var/lib/mysql-backup/mysql-backup.sh

• 备份脚本内容

$  vim /var/lib/mysql-backup/mysql-backup.sh
#!/bin/bash
DATE=`date +%Y%m%d%H%M`                 #every minute
DB_USER=root                            #database username
DB_PASS="mysqlpass"                     #database password
BACKUP=/var/lib/mysql-backup            #backup path
      
#backup command
      
/usr/bin/mysqldump -u$DB_USER -p$DB_PASS -h 127.0.0.1 |gzip > ${BACKUP}\/rainbond_${DATE}.sql.gz
      
#just backup the latest 30 days 
      
find ${BACKUP} -name "rainbond_*.sql.gz" -type f -mtime +30 -exec rm {} \; > /dev/null 2>&1

• 赋予执行权限

chmod +x /var/lib/mysql-backup/mysql-backup.sh