mysql-8.0.22 部署配置
版本
mysql-8.0.22-el7-x86_64.tar.gz
安装方式
二进制安装
安装
复制 mysql-8.0.22-el7-x86_64.tar.gz 文件到目标服务器，解压到 /opt/yidun/mysql-8.0.22-el7-x86_64
创建符号链接 /opt/yidun/mysql --> /opt/yidun/mysql-8.0.22-el7-x86_64
安装服务 /etc/init.d/mysqld
编辑配置文件 /etc/my.cnf
#
# documentation https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
#
[mysqld]
read-only = {% if mysql_replication_role == 'master' %}0{% else %}1{% endif %}
lower_case_table_names = 1
user = {{ mysql_user }}
server_id = {{ mysql_server_id }}
port = {{ mysql_port }}
basedir = {{ mysql_dir }}
datadir = {{ mysql_data_dir }}
socket = {{ mysql_data_dir }}/mysql.sock
log-error = {{ mysql_data_dir }}/mysqld.log
pid-file = {{ mysql_data_dir }}/mysqld.pid
default_authentication_plugin = mysql_native_password
skip_name_resolve = ON
enforce_gtid_consistency = ON
gtid_mode = ON
log-bin = mysql-bin
binlog_format = ROW
expire-logs-days = {{ mysql_expire_logs_days }}
max_connections = {{ mysql_max_connections }}
# character set
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
sql_mode = '{{ mysql_sql_mode }}'
# Remove leading # and set to the amount of RAM for the most important data cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = {{ mysql_innodb_buffer_pool_size }}
# Remove leading # to set options mainly useful for reporting servers. The server defaults are faster for transactions and fast SELECTs. Adjust sizes as needed, experiment to find the optimal values.
join_buffer_size = {{ mysql_join_buffer_size }}
sort_buffer_size = {{ mysql_sort_buffer_size }}
read_rnd_buffer_size = {{ mysql_read_rnd_buffer_size }}
相关参数定义如下
# The Apache mysql version to be downloaded and installed
mysql_version: 8.0.22
mysql_download_url: "https://cdn.mysql.com/archives/mysql-8.0/mysql-{{ mysql_version }}-el7-x86_64.tar.gz"
# The mysql user and group to create files/dirs with and for running the mysql service
mysql_user: mysql
mysql_group: mysql
mysql_port: 3331
mysql_root_dir: /opt/yidun
mysql_download_dir: /tmp/ansible-tmp
mysql_binary: "mysql-{{ mysql_version }}-el7-x86_64.tar.gz"
mysql_dir: "{{ mysql_root_dir }}/mysql"
mysql_hosts: "{{groups['mysql']}}"
mysql_data_dir: /var/lib/mysql
mysql_root_password: "your-root-password"
mysql_sql_mode: 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
mysql_replication_user: repl
mysql_replication_password: repl
# binlog keep 7 days by default
mysql_expire_logs_days: 7
mysql_max_connections: 4096
mysql_innodb_buffer_pool_size: 4096M
mysql_join_buffer_size: 128M
mysql_sort_buffer_size: 2M
mysql_read_rnd_buffer_size: 2M
- 执行初始化
mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql
启动 mysqld 服务 service mysqld start
因为是 initialize-insecure 方式初始化的，root 没有密码，执行脚本设置 root 密码
mysql -u root -S {{ mysql_data_dir }}/mysql.sock -NBe 'flush privileges; ALTER USER "root"@"localhost" IDENTIFIED WITH mysql_native_password BY "{{ mysql_root_password }}'
配置 replication
- 在主节点和从节点分别创建 repl 帐号
CREATE USER IF NOT EXISTS 'repl'@'192.168.122.88' IDENTIFIED BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.122.88';
CREATE USER IF NOT EXISTS 'repl'@'192.168.122.195' IDENTIFIED BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.122.195';
- 相互配置为主从节点
# 从节点
CHANGE MASTER TO
MASTER_HOST='192.168.122.88' ,
MASTER_USER='repl' ,
MASTER_PASSWORD='repl' ,
MASTER_AUTO_POSITION=1;
# 主节点
CHANGE MASTER TO
MASTER_HOST='192.168.122.195' ,
MASTER_USER='repl' ,
MASTER_PASSWORD='repl' ,
MASTER_AUTO_POSITION=1;
- 查看复制状态
mysql> show replica status \G
*************************** 1\. row ***************************
Replica_IO_State: Waiting for master to send event
Source_Host: 192.168.122.88
Source_User: repl
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: mysql-bin.000002
Read_Source_Log_Pos: 2950
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 2661
Relay_Source_Log_File: mysql-bin.000002
Replica_IO_Running: Yes
Replica_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_Source_Log_Pos: 2950
Relay_Log_Space: 2874
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 1
Source_UUID: e23f2357-7be9-11eb-b42f-5254001f9701
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Slave has read all relay log; waiting for more updates
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 0
Network_Namespace:
主备切换操作
如果主节点宕机，需要修改从节点 /etc/my.cnf read-only 配置为 0 （注意 read-only 参数对于 root 帐号不起作用，尽量不要用 root 帐号去连数据库）， 打开可写，然后重启 service mysqld restart，应用写流量切换到从节点
主节点重新起来后，修改 read-only 为 1， 重启 service mysqld restart
数据库连接需要用一个域名或者vip来管理，确保切换过程中要么 100% 切换要么不切换，避免部分应用切换到从库，部分还连着主库，导致双写的情况发生
