- 安装环境
| 系统 | IP | 主机名 | 所需软件 | 硬件 |
|---|---|---|---|---|
| centos7.8 | 192.168.100.105 | master | boost_1_59_0.tar.gz mysql-5.7.12.tar.gz | 内存：4G |
| centos7.8 | 192.168.100.106 | slave | boost_1_59_0.tar.gz mysql-5.7.12.tar.gz | 内存：4G |
- 在master主机上安装ntp时间服务
[root@master ~]# yum -y install ntp
[root@master ~]# sed -i '/^server/s/^/#/g' /etc/ntp.conf
[root@master ~]# cat <<END >>/etc/ntp.conf
server 127.127.1.0
fudge 127.127.1.0 stratum 8
END
[root@master ~]# systemctl start ntpd
[root@master ~]# systemctl enable ntpd
Created symlink from /etc/systemd/system/multi-user.target.wants/ntpd.service to /usr/lib/systemd/system/ntpd.service.- 在slave主机中配置同步master的ntp时间
[root@slave ~]# yum -y install ntpdate
[root@slave ~]# ntpdate 192.168.100.105
[root@slave ~]# echo "/usr/sbin/ntpdate 192.168.100.105" >>/etc/rc.local
[root@slave ~]# chmod +x /etc/rc.local- 安装MySQL数据库，步骤相同，在此安装master
[root@master ~]# yum -y remove mysql-* boost-* ##卸载系统中原有mysql与boost及依赖
[root@master ~]# rpm -qa mysql
[root@master ~]# rpm -qa boost
[root@master ~]# yum -y install gcc gcc-c++ ncurses bison libgcrypt perl cmake ncurses-devel
[root@master ~]# tar -zxvf boost_1_59_0.tar.gz
[root@master ~]# mv boost_1_59_0 /usr/local/boost/
[root@master ~]# groupadd mysql
[root@master ~]# useradd -r -g mysql mysql
[root@master ~]# tar zxvf mysql-5.7.12.tar.gz -C /usr/src/
[root@master ~]# cd /usr/src/mysql-5.7.12/ ##编译多次时，需要删除CMAKE的缓存，rm -rf /usr/src/mysql-5.7.12/CMakeCache.txt
[root@master mysql-5.7.12]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data \
-DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci \
-DMYSQL_TCP_PORT=3306 -DMYSQL_USER=mysql -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 -DENABLE_DOWNLOADS=1 -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/boost -DSYSCONFDIR=/etc
注释：
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql [MySQL安装的根目录]
-DMYSQL_DATADIR=/mydata/mysql/data [MySQL数据库文件存放目录]
-DDEFAULT_CHARSET=utf8 [设置默认字符集为utf8]
-DDEFAULT_COLLATION=utf8_general_ci [设置默认字符校对]
-DMYSQL_TCP_PORT=3306 [MySQL的监听端口]
-DMYSQL_USER=mysql [MySQL用户名]
-DWITH_MYISAM_STORAGE_ENGINE=1 [安装MySQL的myisam数据库引擎]
-DWITH_INNOBASE_STORAGE_ENGINE=1 [安装MySQL的innodb数据库引擎]
-DWITH_ARCHIVE_STORAGE_ENGINE=1 [安装MySQL的archive数据库引擎]
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 [安装MySQL的blackhole数据库引擎]
-DWITH_MEMORY_STORAGE_ENGINE=1 [安装MySQL的memory数据库引擎]
-DENABLE_DOWNLOADS=1 [编译时允许自主下载相关文件]
-DDOWNLOAD_BOOST=1 [允许下载BOOST]
-DWITH_BOOST=/usr/local/boost [指定系统中存在的BOOST]
-DSYSCONFDIR=/etc [MySQL配置文件所在目录]
-DWITH_READLINE=1 [MySQL的readline library]
-DMYSQL_UNIX_ADDR=/var/run/mysql/mysql.sock [MySQL的通讯目录]
-DENABLED_LOCAL_INFILE=1 [启用加载本地数据]
-DWITH_PARTITION_STORAGE_ENGINE=1 [启动mysql的分区存储结构]
-DEXTRA_CHARSETS=all [使MySQL支持所有的扩展字符]
-DWITH_DEBUG=0 [禁用调试模式]
-DMYSQL_MAINTAINER_MODE=0
-DWITH_SSL:STRING=bundled [通讯时支持ssl协议]
-DWITH_ZLIB:STRING=bundled [允许使用zlib library]
[root@master ~]# make -j `cat /proc/cpuinfo | grep processor| wc -l`
[root@master mysql-5.7.12]# make install
[root@master mysql-5.7.12]# chown -R mysql:mysql /usr/local/mysql
[root@master mysql-5.7.12]# cp /usr/src/mysql-5.7.12/support-files/my-default.cnf /etc/my.cnf
[root@master mysql-5.7.12]# cp /usr/src/mysql-5.7.12/support-files/mysql.server /etc/init.d/
[root@master mysql-5.7.12]# chmod +x /etc/init.d/mysql.server
[root@master mysql-5.7.12]# cat <<END >>/usr/lib/systemd/system/mysqld.service
[Unit]
Description=mysqldapi
After=network.target
[Service]
Type=forking
PIDFile=/usr/local/mysql/logs/mysqld.pid
ExecStart=/etc/init.d/mysql.server start
ExecReload=/etc/init.d/mysql.server restart
ExecStop=/etc/init.d/mysql.server stop
PrivateTmp=Flase
[Install]
WantedBy=multi-user.target
END
注解：
PrivateTmp=Flase ##此配置必须关闭，不然mysql连接文件mysql.sock文件会默认生成在以下位置/tmp/systemd-private-83bba738e8ff4837b5ae657eff983821-mysqld.service-BPxWpJ/tmp/mysql.sock，导致数据库无法连接，将此配置项关闭后，则文件正常生成在/tmp/mysql.sock
[root@master mysql-5.7.12]# echo "export PATH=$PATH:/usr/local/mysql/bin/" >>/etc/profile
[root@master mysql-5.7.12]# source /etc/profile
[root@master ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data 初始化
注解：
--initialize-insecure ##禁用mysql的密码策略（密码复杂性等），--initializeaize是开启密码策略，自动生成密码在mysqld.log文件中
--user=mysql ##运行的账户
--basedir=/usr/local/mysql ##mysql的安装位置
--datadir=/usr/local/mysql/data ##mysql数据库服务数据的物理存放路径
[root@master ~]# cat <<END >/etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
character_set_server=utf8
init_connect='SET NAMES utf8'
log-error=/usr/local/mysql/logs/mysqld.log
pid-file=/usr/local/mysql/logs/mysqld.pid
skip-name-resolve
END
注解：
[mysqld] ##声明区域
basedir = /usr/local/mysql ##mysql的安装位置
datadir = /usr/local/mysql/data ##mysql的物理文件存放位置
port = 3306 ##mysql服务监听的端口
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES ##mysql的模式
character_set_server=utf8 ##字符集
init_connect='SET NAMES utf8'
log-error=/usr/local/mysql/logs/mysqld.log ##指定日志文件位置
pid-file=/usr/local/mysql/logs/mysqld.pid ##指定运行服务所产生的pid文件位置
skip-name-resolve ##禁用数据库的反向DNS解析
[root@master ~]# mkdir /usr/local/mysql/logs
[root@master ~]# chown mysql:mysql /usr/local/mysql/logs/
[root@master ~]# systemctl start mysqld
[root@master ~]# systemctl enable mysqld
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.
[root@master ~]# netstat -utpln |grep mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 18723/mysqld- 优化配置master主机的mysql服务
[root@master ~]# vi /etc/my.cnf ##末尾添加
log-bin=master-bin
log-slave-updates
server-id=1
innodb_flush_log_at_trx_commit=2
master_info_repository=table
relay_log_info_repository=TABLE
注解：
log-bin=master-bin ##指定生成二进制文件，可加目录
log-slave-updates ##指定开启slave角色的更新
server-id=1 ##指定id号码
innodb_flush_log_at_trx_commit=2 ##见下方
master_info_repository=table
relay_log_info_repository=TABLE
[root@master ~]# systemctl restart mysqld
[root@master ~]# ls /usr/local/mysql/data/
... master-bin.000001 master-bin.index ...- 优化配置slave主机的mysql
[root@slave ~]# vi /etc/my.cnf ##末尾添加
log-bin=relay1-bin
relay-log=relay1-log-bin
relay-log-index=slave-relay1-bin.index
server-id=2
innodb_flush_log_at_trx_commit=2
slave-parallel-type=LOGICAL_CLOCK
slave_parallel_workers=16
master_info_repository=table
relay_log_info_repository=TABLE
注解：
relay-log=relay-log-bin ##中继日志文件名
relay-log-index=slave-relay-bin.index ##中继日志索引文件
server-id=2 ##id号码
innodb_flush_log_at_trx_commit=2
##指定数据库在存储数据时，事务的刷新方式，如下：
0：该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
1：最安全的，默认值，但是最慢是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘。在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。
2：比0安全，速度比较快，是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。
slave-parallel-type=LOGICAL_CLOCK
##选择并行复制的类型
slave_parallel_workers=16
##worker线程数量
master_info_repository=table
##默认是file，master的更新或者slave的更新直接存放在文件内，若出现故障，会出现数据丢失；若改为table，则为表的方式进行存储更新，支持事务，安全性更高；
relay_log_info_repository=TABLE
##指定中继日志更新，提升性能
[root@slave ~]# systemctl restart mysqld
[root@slave ~]# ls /usr/local/mysql/data/
... relay1-bin.000001 relay1-bin.index ...- 配置master主机上的master角色，配置主节点
[root@master ~]# mysql -uroot -p123123
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
mysql> grant replication slave on *.* to 'linuxfan'@'192.168.100.%' identified by '123123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 | 1008 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> create database linuxfan;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| linuxfan |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
- 配置slave主机的slave角色，配置从节点
[root@slave ~]# mysql -uroot -p123123
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 2 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> change master to master_host='192.168.100.105',master_user='linuxfan',master_password='123123',master_log_file='master-bin.000001',master_log_pos=1008;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.105
Master_User: linuxfan
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 1008
Relay_Log_File: relay1-log-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
- 如果出现以下错误解决
错误1
slave中出现无法识别Master的UUID（虚拟机克隆导致mysql服务的UUID一致冲突）；
解决方法：
[root@slave ~]# rm -rf /usr/local/mysql/data/auto.cnf ##重新生成UUID的配置文件
[root@slave ~]# systemctl restart mysqld错误2：slave中指定的master的position值大于当前值；
解决方法：
[root@slave ~]# mysql -uroot -p123123 ##重新指定master的position值
mysql> stop slave;
mysql> change master to ...
mysql> start slave;- 配置master角色指定主从复制中特定的数据库优化
[root@master ~]# vi /etc/my.cnf
binlog-do-db=linuxfan
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
expire_logs_days=7
注解：
binlog-do-db=linuxfan ##允许同步的数据库名称
binlog-ignore-db=mysql ##不允许同步的数据库名称
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
expire_logs_days=7 ##指定日志存放的时间为7天
[root@master ~]# systemctl restart mysqld
[root@master ~]# mysql -uroot -p123123
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| linuxfan |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use linuxfan;
Database changed
mysql> create table it(岗位 char(16),姓名 char(16),身份证号 char(48),学历 char(16),工资 int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into it values('网络工程师','张三','150404199201091132','高 中','5500');
Query OK, 1 row affected (0.02 sec)
mysql> select * from it;
+-----------------+--------+--------------------+--------+--------+
| 岗位 | 姓名 | 身份证号 | 学历 | 工资 |
+-----------------+--------+--------------------+--------+--------+
| 网络工程师 | 张三 | 150404199201091132 | 高中 | 5500 |
+-----------------+--------+--------------------+--------+--------+
- 验证slave角色的数据库复制情况
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| linuxfan |
| mysql |
| performance_schema |
| sys |
+--------------------+
mysql> use linuxfan;
Database changed
mysql> select * from it;
+-----------------+--------+--------------------+--------+--------+
| 岗位 | 姓名 | 身份证号 | 学历 | 工资 |
+-----------------+--------+--------------------+--------+--------+
| 网络工程师 | 张三 | 150404199201091132 | 高中 | 5500 |
+-----------------+--------+--------------------+--------+--------+
错误：slave中无法找到当前insert语句所在的数据库，错误原因是在之前的同步中出现了失败，导致后续同步无法继续运行；
解决方案：在slave中创建缺少的数据库或者其他数据；
- 查看并设置slave节点sql线程并发数量
mysql> show full processlist;
+-----+-------------+-----------+------+---------+------+--------------------------------------------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-------------+-----------+------+---------+------+--------------------------------------------------------+-----------------------+
| 130 | root | localhost | haha | Query | 0 | starting | show full processlist |
| 131 | system user | | NULL | Connect | 5527 | Waiting for master to send event | NULL |
| 132 | system user | | NULL | Connect | 55 | Slave has read all relay log; waiting for more updates | NULL |
| 133 | system user | | NULL | Connect | 55 | Waiting for an event from Coordinator | NULL |
| 134 | system user | | NULL | Connect | 5527 | Waiting for an event from Coordinator | NULL |
| 135 | system user | | NULL | Connect | 5527 | Waiting for an event from Coordinator | NULL |
| 136 | system user | | NULL | Connect | 5527 | Waiting for an event from Coordinator | NULL |
| 137 | system user | | NULL | Connect | 5527 | Waiting for an event from Coordinator | NULL |
| 138 | system user | | NULL | Connect | 5527 | Waiting for an event from Coordinator | NULL |
| 139 | system user | | NULL | Connect | 5527 | Waiting for an event from Coordinator | NULL |
| 140 | system user | | NULL | Connect | 5527 | Waiting for an event from Coordinator | NULL |
| 141 | system user | | NULL | Connect | 5527 | Waiting for an event from Coordinator | NULL |
| 142 | system user | | NULL | Connect | 5527 | Waiting for an event from Coordinator | NULL |
| 143 | system user | | NULL | Connect | 5527 | Waiting for an event from Coordinator | NULL |
| 144 | system user | | NULL | Connect | 5527 | Waiting for an event from Coordinator | NULL |
| 145 | system user | | NULL | Connect | 5527 | Waiting for an event from Coordinator | NULL |
| 146 | system user | | NULL | Connect | 5527 | Waiting for an event from Coordinator | NULL |
| 147 | system user | | NULL | Connect | 5527 | Waiting for an event from Coordinator | NULL |
| 148 | system user | | NULL | Connect | 5527 | Waiting for an event from Coordinator | NULL |
+-----+-------------+-----------+------+---------+------+--------------------------------------------------------+-----------------------+
19 rows in set (0.00 sec)
mysql> set @@global.slave_parallel_workers=2;
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave sql_thread;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave sql_thread;
Query OK, 0 rows affected (0.03 sec)
mysql> show full processlist;
+-----+-------------+-----------+------+---------+------+--------------------------------------------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-------------+-----------+------+---------+------+--------------------------------------------------------+-----------------------+
| 130 | root | localhost | haha | Query | 0 | starting | show full processlist |
| 131 | system user | | NULL | Connect | 5565 | Waiting for master to send event | NULL |
| 149 | system user | | NULL | Connect | 4 | Slave has read all relay log; waiting for more updates | NULL |
| 150 | system user | | NULL | Connect | 4 | Waiting for an event from Coordinator | NULL |
| 151 | system user | | NULL | Connect | 4 | Waiting for an event from Coordinator | NULL |
+-----+-------------+-----------+------+---------+------+--------------------------------------------------------+-----------------------+
5 rows in set (0.00 sec)