MySQL 安裝
1. Adding the MySQL Yum Repository
Go to the Download MySQL Yum Repository page (https://dev.mysql.com/downloads/repo/yum/) in the MySQL Developer Zone.
Select and download the release package for your platform.
-
Install the downloaded release package
sudo yum install mysql80-community-release-el7-{version-number}.noarch.rpm -
check
yum repolist all | grep mysql
2. installation
sudo yum-config-manager --disable mysql57-community
sudo yum-config-manager --enable mysql80-community
sudo yum install mysql-community-server
3. Starting MySQL Server
sudo systemctl start mysqld.service
sudo systemctl status mysqld.service
4. reset password
shell> sudo grep 'temporary password' /var/log/mysqld.log
shell> mysql -uroot -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';
MySQL 基于事務(wù)的 Replication（利用 GTID 實(shí)現(xiàn)主從復(fù)制）
GTID（全局事務(wù)標(biāo)示符）最初由google實(shí)現(xiàn)，在MySQL 5.6中引入.GTID在事務(wù)提交時(shí)生成，由UUID和事務(wù)ID組成.uuid會(huì)在第一次啟動(dòng)MySQL時(shí)生成，保存在數(shù)據(jù)目錄下的auto .CNF文件里，事務(wù)ID則從1開始自增使用GTID的好處主要有兩點(diǎn)：
不再需要指定傳統(tǒng)復(fù)制中的 master_log_files 和 master_log_pos，使主從復(fù)制更簡單可靠
可以實(shí)現(xiàn)基于庫的多線程復(fù)制，減小主從復(fù)制的延遲
1. 主庫配置
[mysqld]
log-bin=/var/log/mysql/mysql-bin
server-id=1
gtid_mode=ON
enforce_gtid_consistency=1 # 強(qiáng)制執(zhí)行GTID一致性。
2. 創(chuàng)建日志目錄并賦予權(quán)限
shell> mkdir /var/log/mysql
shell> chown mysql.mysql /var/log/mysql
3. 重啟服務(wù)
shell> systemctl restart mysqld.service
4. 在主服務(wù)器創(chuàng)建一個(gè)僅具有復(fù)制過程權(quán)限的單獨(dú)帳戶，以最大程度地降低對(duì)其他帳戶的危害
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'Www.1.com';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
5. 在主服務(wù)器創(chuàng)建一個(gè)僅具有all權(quán)限的單獨(dú)帳戶，用于從服務(wù)器遠(yuǎn)程拷貝原有的數(shù)據(jù)
mysql> CREATE USER 'tom'@'%' IDENTIFIED BY 'Www.1.com';
mysql> GRANT ALL ON *.* TO 'tom'@'%';
mysql> flush privileges;
6. 在從服務(wù)器上使用剛才的用戶進(jìn)行測試連接
shell> mysql -urepl -p'Www.1.com' -hMASTER_IP
shell> mysql -utom -p'Www.1.com' -hMASTER_IP
7. 配置從服務(wù)器 /etc/my.cnf
[mysqld]
server-id=2
gtid_mode=ON
enforce_gtid_consistency=1
# 可選項(xiàng), 把連接到 master 的信息存到數(shù)據(jù)庫中的表中
master-info-repository=TABLE
relay-log-info-repository=TABLE
8. 重啟從服務(wù)器
shell> systemctl restart mysqld.service
9. 進(jìn)入從服務(wù)器mysql，執(zhí)行如下操作
mysql> CHANGE MASTER TO MASTER_HOST='192.168.235.128', MASTER_PORT=3306, MASTER_USER='repl',MASTER_PASSWORD='Www.1.com', master_auto_position=1;
10. 檢查是否成功
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.235.128
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 196
Relay_Log_File: localhost-relay-bin.000004
Relay_Log_Pos: 411
Relay_Master_Log_File: mysql-bin.000002
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
輸出結(jié)果中應(yīng)該看到 I/O 線程和 SQL 線程都是 YES, 就表示成功。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
