fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
fs.aio-max-nr = 1048576
# vm.min_free_kbytes = 524288
vm.swappiness= 5
# vm.nr_hugepages =1024
# vm.hugetlb_shm_group = 2000
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.ip_local_port_range = 1024 65500
1.3 目录规划
目录名称 参数名称 路径地址
安装目录 basedir /usr/local/mysql
数据文件目录 datadir /data/mysql/data
临时文件目录 tmpdir /data/mysql/tmp
socket文件目录 socket /data/mysql/data/mysql.sock
bin日志文件目录 log_bin /datalog/mysql/binlog
relay日志文件目录 relay_log /datalog/mysql/relaylog
1.4 MySQL5.7下载
下载地址：
dev.mysql.com/downloads/mysql
这里统一使用5.7.28的版本
[root@rac1 mysql]# pwd
/package/mysql
[root@rac1 mysql]# ll -rth
total 692M
-rw-r--r-- 1 root root 692M Mar 14 11:49 mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
[root@rac1 mysql]#
1.5 新建用户及目录
[root@rac1 ~]# /usr/sbin/groupadd -g 105 mysql
[root@rac1 ~]# /usr/sbin/useradd -u 105 -g mysql mysql
[root@rac1 ~]# echo "123456" |passwd mysql --stdin
[root@rac1 ~]# mkdir -p /data/mysql/software
[root@rac1 ~]# mkdir -p /usr/local/mysql
[root@rac1 ~]# mkdir -p /data/mysql/data
[root@rac1 ~]# mkdir -p /datalog/mysql/binlog
[root@rac1 ~]# mkdir -p /datalog/mysql/relaylog
[root@rac1 ~]# chown -R mysql:mysql /usr/local/mysql
[root@rac1 ~]# mkdir -p /data/mysql/tmp
[root@rac1 ~]# chown -R mysql:mysql /data/mysql
[root@rac1 ~]# chown -R mysql:mysql /datalog/mysql/
1.6 配置环境变量
[root@rac1 ~]# su - mysql
Attempting to create directory /home/mysql/perl5
[mysql@rac1 ~]$ vim .bash_profile
...
export MYSQL_HOME=/usr/local/mysql
export PATH=$HOME/bin:$MYSQL_HOME/bin:$PATH
export LD_LIBRARY_PATH=$MYSQL_HOME/lib:$LD_LIBRARY_PATH
[mysql@rac1 ~]$ source .bash_profile
1.7 建立配置文件
[root@rac1 ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@rac1 ~]# vim /etc/my.cnf
[mysql]
user =
password =
[mysqld]
#-----------------MySQL Basic Setting-----------------#
server-id = 1723161113
port = 3306
user = mysql
pid-file = mysql.pid
character_set_server = utf8mb4
default_storage_engine = InnoDB
skip_name_resolve = 1
lower_case_table_names = 1
explicit_defaults_for_timestamp = 1
open_files_limit = 65535
max_connections = 1000
max_connect_errors = 100000
basedir = /usr/local/mysql
datadir = /data/mysql/data
tmpdir = /data/mysql/tmp
socket = /data/mysql/data/mysql.sock
query_cache_type = 0
query_cache_size = 0
join_buffer_size = 64M
tmp_table_size = 64M
max_allowed_packet = 32M
read_buffer_size = 16M
read_rnd_buffer_size = 32M
sort_buffer_size = 32M
log_error_verbosity=2
log_timestamps=SYSTEM
#-----------------MySQL Log Setting-----------------#
log_error = mysql-error.log
log_bin = /datalog/mysql/binlog/mysql-bin.log
slow_query_log_file = mysql-slow.log
relay_log = /datalog/mysql/relaylog/mysql-relay.log
log_slave_updates = 1
sync_binlog = 1
relay_log_recovery = 1
binlog_format = row
expire_logs_days = 14
slow_query_log = 1
long_query_time = 2
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 10
log_slow_admin_statements = 1
log_slow_slave_statements = 1
min_examined_row_limit = 1000
#-----------------MySQL Replication Setting-----------------#
slave_skip_errors = ddl_exist_errors
master_info_repository = TABLE
relay_log_info_repository = TABLE
#gtid_mode = on
#enforce_gtid_consistency = 1
binlog_rows_query_log_events = 1
#-----------------MySQL InnoDB Setting-----------------#
innodb_page_size = 16384
innodb_buffer_pool_size = 25600M
innodb_data_file_path = ibdata1:1G:autoextend
innodb_buffer_pool_instances = 8
innodb_file_per_table = 1
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 5
innodb_io_capacity = 800
innodb_io_capacity_max = 2000
innodb_flush_method = O_DIRECT
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_flush_neighbors = 1
innodb_log_file_size = 2G
innodb_log_buffer_size = 16777216
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 67108864
#-----------------MySQL semi Replication Setting-----------------#
#plugin_dir = /usr/local/mysql/lib/plugin
#plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
#loose_rpl_semi_sync_master_enabled = 1
#loose_rpl_semi_sync_slave_enabled = 1
#loose_rpl_semi_sync_master_timeout = 5000
修改my.cnf权限
[root@rac1 ~]# chown mysql.mysql /etc/my.cnf
1.8 依赖包检查
[root@rac1 ~]# rpm -qa libaio*
libaio-0.3.109-13.el7.x86_64
libaio-devel-0.3.109-13.el7.x86_64
[root@rac1 ~]# rpm -qa lvm2-*
lvm2-libs-2.02.177-4.el7.x86_64
lvm2-python-libs-2.02.177-4.el7.x86_64
1.9 解压文件
[root@rac1 ~]# chown mysql.mysql /package/mysql -R
[mysql@rac1 ~]$ cd /package/mysql/
[mysql@rac1 mysql]$ ls
mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
[mysql@rac1 mysql]$ tar -zxvf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz -C /usr/local/mysql/ --strip-components=1
mysql-5.7.28-linux-glibc2.12-x86_64/bin/myisam_ftdump
mysql-5.7.28-linux-glibc2.12-x86_64/bin/myisamchk
mysql-5.7.28-linux-glibc2.12-x86_64/bin/myisamlog
......
1.10 配置服务文件
这里将mysql.server文件拷贝值init.d目录使其可以当作服务启停
[root@rac1 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
这里修改下面几处
[root@rac1 ~]# vim /etc/init.d/mysqld
basedir=/usr/local/mysql
datadir=/data/mysql/data
lockdir='/data/mysql/data'
mysqld_pid_file_path=/data/mysql/data/mysql.pid
1.11 初始化数据库
[mysql@rac1 mysql]$ /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql
[root@rac1 ~]# tail -f /data/mysql/data/mysql-error.log
......
2020-03-14T13:22:12.786017+08:00 1 [Note] A temporary password is generated for root@localhost: 7DO4gs27;YOM
1.12 启动和关闭数据库
[mysql@rac1 mysql]$ /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &
[1] 18642
2020-03-14T05:24:05.258268Z mysqld_safe Logging to '/data/mysql/data/mysql-error.log'.
2020-03-14T05:24:05.320993Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/data
[mysql@rac1 mysql]$ netstat -lntp|grep mysqld
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp6 0 0 :::3306 :::* LISTEN 19567/mysqld
后续可以使用如下命令启停数据库
[mysql@rac1 mysql]$ service mysqld stop
Shutting down MySQL....2020-03-14T05:25:16.603331Z mysqld_safe mysqld from pid file /data/mysql/data/mysql.pid ended
[ OK ]
[1]+ Done /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql
[mysql@rac1 mysql]$ service mysqld start
Starting MySQL.........[ OK ]
设置MySQL自启动
chkconfig mysqld on
1.13 连接数据库
默认密码在error文件中有
[mysql@rac1 mysql]$ cat /data/mysql/data/mysql-error.log |grep password
2020-03-14T13:22:12.786017+08:00 1 [Note] A temporary password is generated for root@localhost: 7DO4gs27;YOM
使用如下命令连接
[mysql@rac1 mysql]$ mysql -S /data/mysql/data/mysql.sock -uroot -p
Enter password: 7DO4gs27;YOM
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.28-log
Copyright (c) 2000, 2019, 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;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
[mysql@rac1 mysql]$ mysql -S /data/mysql/data/mysql.sock -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.28-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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)
2 rac1(187 主),rac3(223 从)配置异步复制
这节我们的内容为MySQL的复制，MySQL复制有两种形式
基于二进制日志文件位置
基于GTID
这节为第一种基于二进制日志文件位置
2.1 开启二进制日志功能
无论是使用哪种方式我们都需要启用二进制日志功能
如果未开启则需要在my.cnf文件中加入如下参数，需要重启数据库生效
---主库187 rac1
[mysqld]
server-id = 1
binlog_format = row
log_bin = /datalog/mysql/binlog/mysql-bin.log
expire_logs_days = 14
log-slave-updates=ON
---从库223 rac3
[mysqld]
server-id = 3
binlog_format = row
log_bin = /datalog/mysql/binlog/mysql-bin.log
expire_logs_days = 14
log-slave-updates=ON
read_only=1
---重启mysql
[mysql@rac1 ~]$ service mysqld stop
Shutting down MySQL.....[ OK ]
[mysql@rac1 ~]$ service mysqld start
Starting MySQL..............[ OK ]
2.2 查看UUID是否一致
需要注意的是如果从库是由主库克隆而来，这时的uuid是一样的，这样也会报错
该文件位于daadir的auto.cnf文件中
vim /data/mysql/data/auto.cnf
如果一样可删除该文件后重新启动数据库即可，这时会生成一个新的文件
2.3 建立复制账号
接下来我们建立一个独立的用于复制的账号
主库和从库
[mysql@rac1 ~]$ mysql -S /data/mysql/data/mysql.sock -uroot -p
mysql> CREATE USER 'repl'@'192.168.2.187' IDENTIFIED BY 'rpl';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.2.187';
mysql> CREATE USER 'repl'@'192.168.2.223' IDENTIFIED BY 'rpl';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.2.223';
mysql> flush privileges;
mysql> select host,user from user;
+---------------+---------------+
| host | user |
+---------------+---------------+
| 192.168.2.187 | repl |
| 192.168.2.223 | repl |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+---------------+---------------+
5 rows in set (0.00 sec)
这里我们限制该账号只能从同步的两台服务器上连接
2.4 备份主库
---创建测试数据(模拟生产数据)
mysql> CREATE DATABASE jumptest DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> use jumptest
Database changed
mysql> source /package/mysql/jumpserver_bak_2020_03_13_22_00_01.sql
---备份
[mysql@rac1 ~]$ mysqldump -S /data/mysql/data/mysql.sock -uroot -p --databases jumptest --single-transaction --master-data=2 --set-gtid-purged=off --triggers --events --routines> /tmp/dumpmaster.sql
Enter password:
[mysql@rac1 ~]$ ll -rth /tmp/dumpmaster.sql
-rw-r--r-- 1 mysql mysql 19M Mar 14 17:04 /tmp/dumpmaster.sql
2.5 文件传输
接下来将主库的dump文件传到备份，之后更改备库的文件权限
主库
[mysql@rac1 ~]$ scp /tmp/dumpmaster.sql root@192.168.2.223:/tmp
从库
[root@rac3 ~]# chown mysql:mysql /tmp/dumpmaster.sql
2.6 备库导入数据
接下来我们将备份的数据导入到备份
[mysql@rac3 ~]$ mysql -S /data/mysql/data/mysql.sock -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.28-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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> CREATE DATABASE jumptest DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.01 sec)
mysql> use jumptest
Database changed
mysql> source /tmp/dumpmaster.sql
2.7 开始同步
接下来我们开启同步
首先我们查看dumpmaster.sql文件中master的信息
mysql> show master status;
+------------------+----------+--------------+------------------+--------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+--------------------------------------------+
| mysql-bin.000004 | 34751569 | | | c1227971-65b3-11ea-bf67-080027839e5c:1-297 |
+------------------+----------+--------------+------------------+--------------------------------------------+
1 row in set (0.00 sec)
---从库开启同步命令2.223
mysql> change master to master_host='192.168.2.187', master_user='repl', master_password='rpl',master_log_file='mysql-bin.000004',master_log_pos= 34751569;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
2.8 从库查看同步状态
使用如下命令查看同步是否正常
主要关注如下几点
Slave_IO_Running需要为YES
Slave_SQL_Running需要为YES
Seconds_Behind_Master需要为0
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.187
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 34751569
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......
Seconds_Behind_Master: 0
......
2.9 重启和重置复制
使用如下命令关闭重启
mysql>stop slave;
mysql>start slave;
我们可以独立的重启IO进程或者SQL进程
mysql>stop slave sql_thread;
mysql>stop slave io_thread;
mysql>start slave io_thread;
mysql>start slave sql_thread;
使用如下命令重置复制
mysql>reset slave all;
3 rac1(187 主),rac3(188 从)配置半同步复制
3.1 半同步介绍
异步的复制，主库将二进制日志发送到从库后并不需要确认从库是否接受并应用，这时就可能会造成数据丢失。
MySQL 从5.5版本后推出了半同步的功能，相当于Oracle DG的最大保护模式，它要求从库在接收并应用日志后，主库才提交完成，保证了数据。
开启半同步需要如下要求：
MySQL 5.5及以上版本
变量have_dynamic_loading为YES
3.2 188从库初始化主库数据
188：
mysql> CREATE DATABASE jumptest DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.01 sec)
mysql> use jumptest
Database changed
mysql> source /tmp/dumpmaster.sql
3.3 创建复制账号
[mysql@rac1 ~]$ mysql -S /data/mysql/data/mysql.sock -uroot -p
mysql> CREATE USER 'repl'@'192.168.2.187' IDENTIFIED BY 'rpl';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.2.187';
mysql> CREATE USER 'repl'@'192.168.2.188' IDENTIFIED BY 'rpl';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.2.188';
mysql> flush privileges;
mysql> use mysql
---从库188
mysql> select host,user from user;
+---------------+---------------+
| host | user |
+---------------+---------------+
| 192.168.2.187 | repl |
| 192.168.2.188 | repl |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+---------------+---------------+
5 rows in set (0.00 sec)
---主库187
mysql> select host,user from user;
+---------------+---------------+
| host | user |
+---------------+---------------+
| 192.168.2.187 | repl |
| 192.168.2.188 | repl |
| 192.168.2.223 | repl |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+---------------+---------------+
6 rows in set (0.00 sec)
3.4 加载半同步插件
因需执行INSTALL PLUGIN, SET GLOBAL, STOP SLAVE和START SLAVE操作，所以用户需有SUPER权限
主库和从库
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
这里安装半同步的master和slave插件
考虑到后面主从可能需要切换，这里在主从库上都安装
确认是否加载成功
mysql> show plugins;
......
| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |
| rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |
+----------------------------+----------+--------------------+--------------------+---------+
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
| rpl_semi_sync_slave | ACTIVE |
+----------------------+---------------+
2 rows in set (0.00 sec)
3.5 启用半同步
首先我们启用半同步插件
主库 187
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
从库 188
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
mysql> change master to master_host='192.168.2.187', master_user='repl', master_password='rpl',master_log_file='mysql-bin.000004',master_log_pos= 34752186;
mysql> start slave;
mysql> show slave status \G;
之后需要重启IO线程才能使半同步生效，也可直接重启复制
从库
mysql> STOP SLAVE IO_THREAD;
mysql> START SLAVE IO_THREAD;
如果从库超过一定时间不能和主库进行通信，则会自动降为异步模式
该时间由rpl_semi_sync_master_timeout参数控制，单位为毫秒
3.6 查看半同步状态
我们使用如下命令查看半同步是否正常工作
主库的master和从库的slave都需要为ON
主库
mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON |
+-----------------------------+-------+
1 row in set (0.02 sec)
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)
从库
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | OFF |
+-----------------------------+-------+
1 row in set (0.01 sec)
3.7 写入配置文件
接下来我们将命令写在配置文件中以使重启后自动启动
考虑到后面主从可能需要切换，这里在主从库上半同步的master和slave都设置为启动
主库187和从库188
[mysql@rac1 ~]$ vim /etc/my.cnf
#-----------------MySQL semi Replication Setting-----------------#
plugin_dir = /usr/local/mysql/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_slave_enabled = 1
rpl_semi_sync_master_timeout = 5000
--从库 188,223
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
主187、从库188 重启生效：
[mysql@rac1 ~]$ service mysqld restart
Shutting down MySQL....[ OK ]
Starting MySQL.........[ OK ]
测试数据同步
主库187
mysql> create table cjc01(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into cjc01 values(1);
Query OK, 1 row affected (0.02 sec)
mysql> insert into cjc01 values(2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into cjc01 values(3);
Query OK, 1 row affected (0.01 sec)
mysql> update cjc01 set id=100 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> delete from cjc01 where id=2;
Query OK, 1 row affected (0.00 sec)
mysql> select * from cjc01;
+------+
| id |
+------+
| 100 |
| 3 |
+------+
2 rows in set (0.00 sec)
从库188
mysql> select * from cjc01;
+------+
| id |
+------+
| 100 |
| 3 |
+------+
2 rows in set (0.00 sec)
从库223
mysql> select * from cjc01;
+------+
| id |
+------+
| 100 |
| 3 |
+------+
2 rows in set (0.00 sec)
关于Mysql MHA部署中什么是主从复制就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
网站名称：MysqlMHA部署中什么是主从复制
当前路径：http://cdkjz.cn/article/ppppge.html
