注意:
负责在主、从服务器传输各种修改动作的媒介是主服务器的二进制变更日志,这个日志记载着需要传输给从服务器的各种修改动作。因此,主服务器必须激活二进制日志功能。从服务器必须具备足以让它连接主服务器并请求主服务器把二进制变更日志传输给它的权限。
1,确认是否存在mysql用户
yum安装finger
root@n3:~# yum install finger
查看是否存在mysql用户
root@n3:~# finger mysql
Login: mysql Name: MySQL Server
Directory: /var/lib/mysql Shell: /bin/bash
Never logged in.
No mail.
No Plan.
说明存在。若
root@n3:~# finger as
finger: as: no such user.
说明不存在as用户
2,检查mysql默认使用的3306端口是否被占用
[root@oracle_test ~]# yum install netstat
[root@oracle_test ~]# netstat -nat |grep 3306
[root@oracle_test ~]# netstat -nat |grep LISTEN
tcp 0 0 0.0.0.0:6379 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:6010 0.0.0.0:* LISTEN
tcp 0 0 :::6379 :::* LISTEN
tcp 0 0 :::13775 :::* LISTEN
tcp 0 0 :::42065 :::* LISTEN
tcp 0 0 :::1521 :::* LISTEN
tcp 0 0 :::22 :::* LISTEN
tcp 0 0 ::1:25 :::* LISTEN
tcp 0 0 ::1:6010 :::* LISTEN
说明没有被占用
3,安装一系列步骤
[
root@oracle_test ~]# mkdir -p /u01/app/software
[
root@oracle_test ~]# cd /u01/app/software/
[
root@oracle_test software]# ls
mysql-5.7.11-1.el6.x86_64.rpm-bundle.tar
[
root@oracle_test software]# tar -xvf mysql-5.7.11-1.el6.x86_64.rpm-bundle.tar
[
root@oracle_test software]# chown -R mysql:mysql mysql-*
[
root@oracle_test software]# ll
total 858408
-rw-r--r-- 1 mysql mysql 439500800 May 17 02:44 mysql-5.7.11-1.el6.x86_64.rpm-bundle.tar
-rw-r--r-- 1 mysql mysql 24176228 Feb 2 17:31 mysql-community-client-5.7.11-1.el6.x86_64.rpm
-rw-r--r-- 1 mysql mysql 333480 Feb 2 17:31 mysql-community-common-5.7.11-1.el6.x86_64.rpm
-rw-r--r-- 1 mysql mysql 3854020 Feb 2 17:31 mysql-community-devel-5.7.11-1.el6.x86_64.rpm
-rw-r--r-- 1 mysql mysql 38247128 Feb 2 17:31 mysql-community-embedded-5.7.11-1.el6.x86_64.rpm
-rw-r--r-- 1 mysql mysql 130605584 Feb 2 17:32 mysql-community-embedded-devel-5.7.11-1.el6.x86_64.rpm
-rw-r--r-- 1 mysql mysql 2237704 Feb 2 17:32 mysql-community-libs-5.7.11-1.el6.x86_64.rpm
-rw-r--r-- 1 mysql mysql 1722544 Feb 2 17:32 mysql-community-libs-compat-5.7.11-1.el6.x86_64.rpm
-rw-r--r-- 1 mysql mysql 141256272 Feb 2 17:33 mysql-community-server-5.7.11-1.el6.x86_64.rpm
-rw-r--r-- 1 mysql mysql 97051252 Feb 2 17:34 mysql-community-test-5.7.11-1.el6.x86_64.rpm
4,rpm方式安装
查看CentOS自带MySQL 5.1组件并卸载
[
root@oracle_test software]# rpm -qa | grep -i mysql
mysql-libs-5.1.73-5.el6_6.x86_64
mysql-community-common-5.7.11-1.el6.x86_64
卸载MySQL 5.1组件
[root@oracle_test software]# rpm -e --nodeps mysql-libs-5.1.73-5.el6_6.x86_64
安装依赖(MySQL 5.5并不需要这个依赖,但是5.7必须要,否则server组件无法安装成功)
yum -y install numactl
按顺序安装
[
root@oracle_test software]# rpm -ivh mysql-community-common-5.7.11-1.el6.x86_64.rpm
warning: mysql-community-common-5.7.11-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:mysql-community-common ########################################### [100%]
也可以
[
root@oracle_test software]# rpm -ivh mysql-community-common-5.7.11-1.el6.x86_64.rpm --force
[
root@oracle_test software]# rpm -ivh mysql-community-libs-5.7.11-1.el6.x86_64.rpm
warning: mysql-community-libs-5.7.11-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:mysql-community-libs ########################################### [100%]
[
root@oracle_test software]# rpm -ivh mysql-community-client-5.7.11-1.el6.x86_64.rpm
warning: mysql-community-client-5.7.11-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:mysql-community-client ########################################### [100%]
[
root@oracle_test software]# rpm -ivh mysql-community-server-5.7.11-1.el6.x86_64.rpm
warning: mysql-community-server-5.7.11-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:mysql-community-server ########################################### [100%]
若出现
[
root@T4 software]# rpm -ivh mysql-community-server-5.7.11-1.el6.x86_64.rpm
warning: mysql-community-server-5.7.11-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
error: Failed dependencies:
/usr/bin/perl is needed by mysql-community-server-5.7.11-1.el6.x86_64
libnuma.so.1()(64bit) is needed by mysql-community-server-5.7.11-1.el6.x86_64
perl(File::Path) is needed by mysql-community-server-5.7.11-1.el6.x86_64
perl(Getopt::Long) is needed by mysql-community-server-5.7.11-1.el6.x86_64
perl(POSIX) is needed by mysql-community-server-5.7.11-1.el6.x86_64
perl(strict) is needed by mysql-community-server-5.7.11-1.el6.x86_64
是因为系统缺少perl和libnuma.so*
[
root@T4 software]# yum install libnuma.so*
[
root@T4 software]# yum install perl
安装即可
启动MySQL(下面的命令请执行两次。非常奇怪,不知为什么第一次一定失败,第二次却一定成功)
[
root@oracle_test software]# service mysqld start
Initializing MySQL database: [ OK ]
Installing validate password plugin: [ OK ]
Starting mysqld: [ OK ]
[
root@oracle_test software]# service mysqld start
Starting mysqld: [ OK ]
获得MySQL初始密码
[
root@oracle_test software]# grep 'temporary password' /var/log/mysqld.log
2016-05-16T19:55:52.577402Z 1 [Note] A temporary password is generated for
root@localhost: qDsrapO9W:8q
使用上一步获得的密码登录MySQL,并修改初始密码
[
root@oracle_test software]# mysql -uroot -p'qDsrapO9W:8q'
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 4
Server version: 5.7.11
Copyright (c) 2000, 2016, 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>
修改密码为,Password$1
mysql> alter user 'root'@'localhost' identified by 'Password$1';
Query OK, 0 rows affected (0.00 sec)
之后还可以放开防火墙、设定自启动、增强MySQL安全设置等。
iptables -I INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
/etc/init.d/iptables save
chkconfig mysqld on
mysql_secure_installation
主从GTID模式安装
主库
[
root@oracle_test software]# vi /etc/my.cnf
添加
binlog-format = ROW
log-bin = master-bin
log-bin-index = master-bin.index
log-slave-updates = true
此种模式从库日志才能被ogg捕捉
gtid-mode = on
enforce-gtid-consistency = true
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
report-host = 172.16.57.53
server-id = 1
关于以上参数的解释
GTID的作用
根据GTID可以知道事务最初是在哪个实例上提交的
GTID的存在方便了Replication的Failover
重启数据库
[
root@oracle_test software]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
查看gtid信息:
[
root@oracle_test software]# mysql -uroot -p'Password$1'
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 3
Server version: 5.7.11-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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 global variables like '%GTID%';
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-------+
8 rows in set (0.00 sec)
主服务器配置同步复制帐号
mysql> grant replication slave on *.* to 'repl'@'%' identified by 'Password$1';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
从库
[
root@T4 software]# vi /etc/my.cnf
添加
binlog-format = ROW
log-bin = mysql-bin
relay-log = slave-relay-bin
relay-log-index = slave-relay-bin.index
log-slave-updates = true
gtid-mode = on
enforce-gtid-consistency = true
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
report-host = 172.16.57.57
server-id = 11
重启数据库:
[
root@T4 software]# service mysqld restart
停止 mysqld: [确定]
初始化 MySQL 数据库: [确定]
Installing validate password plugin: [确定]
正在启动 mysqld: [确定]
[
root@T4 software]# service mysqld start
正在启动 mysqld: [确定]
查看gtid状态:
[
root@T4 software]# mysql -uroot -p'Password$1'
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 5
Server version: 5.7.11-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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 global variables like '%GTID%';
+----------------------------------+--------------------------------------------+
| Variable_name | Value |
+----------------------------------+--------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | ebd02802-1b2a-11e6-a990-52540034e4ef:1-135 |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+--------------------------------------------+
8 rows in set (0.00 sec)
连接主Mysql,配置主从
(1) 连接主数据库
在从库运行
mysql> CHANGE MASTER TO MASTER_HOST='172.16.57.53',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='Password$1',MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
若出现
mysql> CHANGE MASTER TO MASTER_HOST='172.16.57.53',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='Password$1',MASTER_AUTO_POSITION=1;
ERROR 3081 (HY000): This operation cannot be performed with running replication threads; run STOP SLAVE FOR CHANNEL '' first
解决方法:
1.停掉slave服务
mysql> stop slave;
Query OK, 0 rows affected (2.01 sec)
2.重置slave服务
mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)
开启服务
mysql> start slave
;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.57.53
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 589
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 804
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_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_Master_Log_Pos: 589
Relay_Log_Space: 1011
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 31cf20aa-1ba0-11e6-af7a-52540032a247
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 31cf20aa-1ba0-11e6-af7a-52540032a247:1-2
Executed_Gtid_Set: 31cf20aa-1ba0-11e6-af7a-52540032a247:1-2,
ebd02802-1b2a-11e6-a990-52540034e4ef:1-135
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
说明成功
验证同步情况
(1) 主数据库创建一个数据库
mysql> create database fafa;
Query OK, 1 row affected (0.00 sec)
mysql> show master status\G;
*************************** 1. row ***************************
File: master-bin.000001
Position: 748
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 31cf20aa-1ba0-11e6-af7a-52540032a247:1-3
1 row in set (0.00 sec)
ERROR:
No query specified
查看所建的表是否存在
主库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| fafa |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
从库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| fafa |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)