1、搭建准备
安装包 下载地址：https://dev.mysql.com/downloads/mysql/5.7.html mysql-5.7.20-1.el6.x86_64.rpm-bundle.tar 解压&顺序安装
2、搭建开始
# 解压安装包 [root@mvxl6448 soft]# tar xvf mysql-5.7.20-1.el6.x86_64.rpm-bundle.tar [root@mvxl6448 soft]# ll total 922628 -rw-r--r-- 1 apps apps 472381440 Oct 20 14:35 mysql-5.7.20-1.el6.x86_64.rpm-bundle.tar -rw-r--r-- 1 apps apps 23814860 Sep 14 23:44 mysql-community-client-5.7.20-1.el6.x86_64.rpm -rw-r--r-- 1 apps apps 336476 Sep 14 23:44 mysql-community-common-5.7.20-1.el6.x86_64.rpm -rw-r--r-- 1 apps apps 3748072 Sep 14 23:44 mysql-community-devel-5.7.20-1.el6.x86_64.rpm -rw-r--r-- 1 apps apps 39278972 Sep 14 23:44 mysql-community-embedded-5.7.20-1.el6.x86_64.rpm -rw-r--r-- 1 apps apps 136172856 Sep 14 23:44 mysql-community-embedded-devel-5.7.20-1.el6.x86_64.rpm -rw-r--r-- 1 apps apps 2177676 Sep 14 23:44 mysql-community-libs-5.7.20-1.el6.x86_64.rpm -rw-r--r-- 1 apps apps 1723320 Sep 14 23:44 mysql-community-libs-compat-5.7.20-1.el6.x86_64.rpm -rw-r--r-- 1 apps apps 159637208 Sep 14 23:45 mysql-community-server-5.7.20-1.el6.x86_64.rpm -rw-r--r-- 1 apps apps 105476736 Sep 14 23:45 mysql-community-test-5.7.20-1.el6.x86_64.rpm # 顺序执行安装命令 [root@mvxl6448 soft]# rpm -ivh mysql-community-common-5.7.20-1.el6.x86_64.rpm [root@mvxl6448 soft]# rpm -ivh mysql-community-libs-5.7.20-1.el6.x86_64.rpm [root@mvxl6448 soft]# rpm -ivh mysql-community-libs-compat-5.7.20-1.el6.x86_64.rpm [root@mvxl6448 soft]# rpm -ivh mysql-community-client-5.7.20-1.el6.x86_64.rpm [root@mvxl6448 soft]# rpm -ivh mysql-community-server-5.7.20-1.el6.x86_64.rpm # 如果仅仅是安装MySql Server，安装到这里就可以了. [root@mvxl6448 soft]# yum install perl-JSON.noarch [root@mvxl6448 soft]# yum install perl-Time-HiRes [root@mvxl6448 soft]# rpm -ivh mysql-community-test-5.7.20-1.el6.x86_64.rpm [root@mvxl6448 soft]# rpm -ivh mysql-community-embedded-5.7.20-1.el6.x86_64.rpm [root@mvxl6448 soft]# rpm -ivh mysql-community-devel-5.7.20-1.el6.x86_64.rpm [root@mvxl6448 soft]# rpm -ivh mysql-community-embedded-devel-5.7.20-1.el6.x86_64.rpm
安装之前先卸载linux自带的msyql rpm -qa|grep -i mysql 删除命令：rpm -e –nodeps 包名
Master配置 /etc/my.cnf
[client] default-character-set=utf8 [mysqld] character-set-server=utf8 binlog-format=ROW transaction-isolation=REPEATABLE-READ log-slave-updates=true gtid-mode=on # GTID only enforce-gtid-consistency=true # GTID only master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 slave-parallel-workers=2 binlog-checksum=CRC32 master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 innodb_flush_log_at_trx_commit=1 sync_binlog=1 #同步复制的database replicate-do-db=a replicate-do-db=b replicate-do-db=c replicate-do-db=d server-id=1 report-port=3306 port=3306 log-bin=master-bin.log symbolic-links=0 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid explicit_defaults_for_timestamp=1 report-host=master sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES lower_case_table_names=1 [mysql] default-character-set=utf8
Slaver配置 /etc/my.cnf
[client] default-character-set=utf8 [mysqld] character-set-server=utf8 binlog-format=ROW transaction-isolation=REPEATABLE-READ log-slave-updates=true gtid-mode=on # GTID only enforce-gtid-consistency=true # GTID only master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 slave-parallel-workers=2 binlog-checksum=CRC32 master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 innodb_flush_log_at_trx_commit=1 sync_binlog=1 #同步复制的database replicate-do-db=cdp_docker replicate-do-db=erp_adapter replicate-do-db=smartexpense replicate-do-db=smartintegration replicate-do-db=dispatch replicate-do-db=job_trace #测试使用 replicate-do-db=test server-id=2 report-port=3306 port=3306 log-bin=slave-bin.log symbolic-links=0 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid report-host=slave lower_case_table_names=1 log_slave_updates = 1 #添加(将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启) replicate-same-server-id=0 #添加(防止MySQL循环更新) relay_log_recovery = 1 #添加(MySQLrelay_log的自动修复功能) [mysql] default-character-set=utf8
3、启动Mysql
启动命令 service mysqld start 检查mysql状态 service mysqld status 查找MySql root用户密码 grep 'temporary password' /var/log/mysqld.log 修改数据库Root 用户密码 shell> mysql -uroot -p mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';
4、MySql 主从搭建
1、在master数据库上创建复制用户 repl_user
# 执行sql: GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'从库IP地址' IDENTIFIED BY 'abc@123'；
2、锁定主数据库、记录Binlog位置以及备份主数据库
mysql -h 127.0.0.1 -P 3306 -u root -p --prompt='master> ' master> FLUSH TABLES WITH READ LOCK; master> SHOW MASTER STATUS;
3、转储Master节点上的目标数据库(ex:a（初始化建库语句）)数据
# 方法一： mysqldump -h 127.0.0.1 -P 3306 -u root -p -B a > a.sql # 方法二：不带gtid数据，不推荐； mysqldump -h 127.0.0.1 -P 3306 -u root -p -B a--set-gtid-purged=OFF > a.sql
4、在slave上初始化目标数据库 建库语句 手动复制到从库服务器的目录下
mysql -h 127.0.0.1 -P 3306 -u root -p < a.sql
5、mysql -h 127.0.0.1 -P 3306 -u root -p < a.sql
mysql -h 127.0.0.1 -P 3306 -u root -p --prompt='slave> ' slave> CHANGE MASTER TO MASTER_HOST='主库IP地址', MASTER_USER='repl_user', MASTER_PASSWORD='abc@123', MASTER_AUTO_POSITION=1; slave> START SLAVE;6、基本检查
master> UNLOCK TABLES; master> USE plms; master> CREATE TABLE simples (id INT NOT NULL PRIMARY KEY); master> INSERT INTO plms.simples VALUES (1),(2),(3),(999); slave> SELECT * FROM plms.simples; +-----+ | id | +-----+ | 1 | | 2 | | 3 | | 999 | +-----+
7、备注
当出现主从备份问题时最好重新设置主从备份： 锁住主库： mysql -h 127.0.0.1 -P 3306 -u root -p --prompt='master> ' master> FLUSH TABLES WITH READ LOCK; master> SHOW MASTER STATUS; 记下master_log_file、master_log_pos 1、在主库备份 mysqldump -h 127.0.0.1 -P 3306 -u root -p -B plms > plms.sql 2、去从库初始化 mysql -h 127.0.0.1 -P 3306 -u root -p < plms.sql stop slave; change master to master_host='主库机器', master_user='repl_user', master_password='abc@123', master_port=3306, master_log_file= '主库查status的结果 ', master_log_pos= 主库查status的结果; mysql> start slave ; 3、主库解锁 UNLOCK TABLES;