MYSQL5.6.40原始碼安裝 主從搭建 主主搭建

前言：
前面那篇5.6.38主從搭建的文章屬於測試系統裡面弄著玩的，以下這篇文章寫的是在公司生產庫上搭建的MYSQL主從。
在上系統前，糾結了很久，到底使用哪種資料庫（PG？HBASE？MONGODB？）？最後選擇了MYSQL是由於公司懂的人多，自己也比較熟悉，且新專案初期資料量不大，MYSQL可以滿足需求，最最重要的是這個新專案屬於探路性質的，如果真的被重視起來，那到時再把資料遷移出去就好了（基於開發說的資料結構簡單單一，儲存資料單一的情況，沒有特別的資料型別等）。
所以最終選擇了開源資料庫中的一員MYSQL。（本來還有考慮ORACLE的，老本行，只不過考慮到領導們支援開源，那就用開源吧）

之前也糾結了要不要使用mysql5.7.22，結果去下載了安裝，發現redhat6.5的cmake版本太低，不支援mysql5.7.22的編譯，一下子又沒找到redhat7的安裝包，乾脆就用mysql5.6.40這個5.6的最終穩定版算了。

作業系統，網路卡，防火牆，IP地址配置等等在這裡就不寫了。


一、MYSQL原始碼安裝
（兩個節點都安裝，步驟一樣的）

安裝環境：
作業系統REDHAT6.5

NODE1 主機名 master      IP地址 192.168.159.148

NODE2 主機名 slave       IP地址 192.168.159.149

1、安裝依賴
yum -y install make gcc-c++ cmake bison-devel  ncurses-devel perl
2、建立使用者和組 
groupadd mysql
useradd mysql -g mysql -M -s /sbin/nologin
3、下載5.6.40原始碼包

4、解壓tar -zxvf mysql-5.6.40.tar.gz

cd /opt/

tar -zxvf mysql-5.6.40.tar.gz

cd  mysql-5.6.40

5、編譯安裝
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DSYSCONFDIR=/etc-DWITH_MYISAM_STORAGE_ENGINE=1-DWITH_INNOBASE_STORAGE_ENGINE=1-DWITH_MEMORY_STORAGE_ENGINE=1-DWITH_READLINE=1-DMYSQL_UNIX_ADDR=/tmp/mysqld.sock -DMYSQL_TCP_PORT=3306-DENABLED_LOCAL_INFILE=1-DWITH_PARTITION_STORAGE_ENGINE=1-DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci
說明：
設定安裝目錄
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql
資料庫存放目錄
-DMYSQL_DATADIR=/usr/local/mysql/data
系統配置目錄
-DSYSCONFDIR=/etc
安裝 myisam 儲存引擎

-DWITH_MYISAM_STORAGE_ENGINE=1
安裝 innodb 儲存引擎
-DWITH_INNOBASE_STORAGE_ENGINE=1 
安裝memory儲存引擎
-DWITH_MEMORY_STORAGE_ENGINE=1
快捷鍵功能
-DWITH_READLINE=1
Unix socket檔案路徑
-DMYSQL_UNIX_ADDR=/tmp/mysqld.sock
MySQL 監聽埠
-DMYSQL_TCP_PORT=3306
允許從本地匯入資料
-DENABLED_LOCAL_INFILE=1
安裝資料庫分割槽
-DWITH_PARTITION_STORAGE_ENGINE=1 
安裝所有擴充套件字符集
-DEXTRA_CHARSETS=all
使用 utf8 字元
-DDEFAULT_CHARSET=utf8 
校驗字元
-DDEFAULT_COLLATION=utf8_general_ci
支援 SSL
-DWITH_SSL=yes

make &&make install

6、目錄授權
chown mysql.mysql /usr/local/mysql
mkdir /usr/local/mysql/log
chown mysql.mysql /usr/local/mysql/log
7、資料庫初始化
cd /usr/local/mysql
./scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data
8、配置環境變數
vi /root/.bash_profile 在最後一行新增
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin:/usr/local/mysql/lib

二、MYSQL5.6.40主從搭建

1、Master端配置部署

a、在主伺服器上的my.cnf配置檔案中的[mysqld]節點下新增以下配置

vi /etc/my.cnf

[mysqld]

說明：

log-bin ：給出二進位制日誌的所有檔案基礎名

log-bin-index ：給出二進位制日誌檔案的檔名，通常以000001開始，順序遞增。全名：master-bin.000001

server-id ：mysql伺服器唯一ID，在主從複製的所有伺服器中必須唯一。

create user repl_user;

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY PASSWORD '******';

設定密碼時會遇到報錯：

ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number

解決辦法：用select password('你想輸入的密碼');查詢出你的密碼對應的字串

select password('123456');

查出的是*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9

GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';

2、Slave端配置部署

3、建立主從同步

（重建備庫也是使用該方法）

建立主從同步可以從主庫上匯出資料，也可以從已有的從庫上匯出資料，然後再匯入到新的從庫中，change master to建立同步。

（如果是新搭建主從，可以直接按照安裝主庫的方法直接安裝從庫就可以了，都是全新的，就不需要做下面的匯出和匯入操作）

3.1 、匯出資料

在主庫上匯出資料：

mysqldump -u***-p***-S /data/mysql6001/mysql.sock        --default-character-set=utf8 –q --single-transaction --master-data-A >  /tmp/all_database.sql

（或者）在從庫上匯出資料：

mysqldump -u***-p***-S /data/mysql6001/mysql.sock --default-character-set=utf8 -q --single-transaction --dump-slave-A  >  /tmp/all_database.sql

NOTES：

--master-data和--dump-slave匯出的備份中，會包含master_log_file和master_log_pos資訊。

例子：

mysqldump -uroot --events --all-databases > /opt/mysql.dump

3.2、從庫匯入資料

mysql -u*** -p*** --default-character-set=utf8< all_database.sql

例子：

mysql -uroot -p*** <  /opt/mysql.dump

3.3、從庫與主機建立同步

驗證主從是否搭建成功在從庫執行
show slave status \G 
在主庫建立一個表
use test
create table aa (name char(10));
insert into aa values('Tom');
在從庫查詢
use test
select * from aa;
查到剛剛插入的資料就O了

修改root密碼

cd /usr/local/ mysql /bin

./mysqladmin -u root password

mysql> use mysql;

mysql> desc user;

mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "root"; //授權遠端連線

mysql> update user set Password = password('123456') where User='root'; //設定root使用者密碼

mysql> select Host,User,Password from user where User='root';

mysql> flush privileges;

mysql> exit

二、MYSQL5.6.40主主搭建
前面的安裝配置都一樣，只需要配置/etc/my.cnf

1、主庫A配置（192.168.159.148）
vi /etc/my.cnf

[client]
port = 3306 socket = /tmp/mysql.sock
[mysqld]
basedir = /usr/local/mysql
port = 3306 socket = /tmp/mysql.sock
datadir = /usr/local/mysql/data
pid-file = /usr/local/mysql/data/mysql.pid
log-error = /usr/local/mysql/data/mysql.err
server-id = 1 auto_increment_offset = 1 auto_increment_increment = 2 #奇數ID
log-bin = mysql-bin                                                     #開啟二進位制功能,MASTER主伺服器必須開啟此項
binlog-format=ROW
#binlog-row-p_w_picpath=minimal  #這個引數不知道為何會報錯
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=0 sync_binlog=0 binlog-checksum=CRC32
master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 #expire_logs_days=5 max_binlog_size=1024M                                                   #binlog單檔案最大值
replicate-ignore-db = mysql                                             #忽略不同步主從的資料庫
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = test
replicate-ignore-db = zabbix
max_connections = 3000 max_connect_errors = 30 skip-character-set-client-handshake                                     #忽略應用程式想要設定的其他字符集
init-connect='SET NAMES utf8' #連線時執行的SQL
character-set-server=utf8                                               #服務端預設字符集
wait_timeout=1800 #請求的最大連線時間
interactive_timeout=1800 #和上一引數同時修改才會生效
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES                     #sql模式
max_allowed_packet = 10M
bulk_insert_buffer_size = 8M
query_cache_type = 1 query_cache_size = 128M
query_cache_limit = 4M
key_buffer_size = 256M
read_buffer_size = 16K
skip-name-resolve
slow_query_log=1 long_query_time = 6 slow_query_log_file=slow-query.log
innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[mysqldump]
quick
max_allowed_packet = 16M
[mysqld_safe]

2、主庫B配置（192.168.159.149）
vi /etc/my.cnf

[client]
port = 3306 socket = /tmp/mysql.sock
[mysqld]
basedir = /usr/local/mysql
port = 3306 socket = /tmp/mysql.sock
datadir = /usr/local/mysql/data
pid-file = /usr/local/mysql/data/mysql.pid
log-error = /usr/local/mysql/data/mysql.err
server-id = 2 auto_increment_offset = 2 auto_increment_increment = 2 #偶數ID
log-bin = mysql-bin                                                     #開啟二進位制功能,MASTER主伺服器必須開啟此項
binlog-format=ROW
#binlog-row-p_w_picpath=minimal #這個引數不知道為何會報錯 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=0 sync_binlog=0 binlog-checksum=CRC32
master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 #expire_logs_days=5 max_binlog_size=1024M                                                   #binlog單檔案最大值
replicate-ignore-db = mysql                                             #忽略不同步主從的資料庫
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = test
replicate-ignore-db = zabbix
max_connections = 3000 max_connect_errors = 30 skip-character-set-client-handshake                                     #忽略應用程式想要設定的其他字符集
init-connect='SET NAMES utf8' #連線時執行的SQL
character-set-server=utf8                                               #服務端預設字符集
wait_timeout=1800 #請求的最大連線時間
interactive_timeout=1800 #和上一引數同時修改才會生效
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES                     #sql模式
max_allowed_packet = 10M
bulk_insert_buffer_size = 8M
query_cache_type = 1 query_cache_size = 128M
query_cache_limit = 4M
key_buffer_size = 256M
read_buffer_size = 16K
skip-name-resolve
slow_query_log=1 long_query_time = 6 slow_query_log_file=slow-query.log
innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[mysqldump]
quick
max_allowed_packet = 16M
[mysqld_safe]

3、主庫A（192.168.159.148）建立同步使用者

mysql> grant replication slave on *.* to 'repl'@'192.168.159.149' identified by '123456';
mysql> flush privileges;

4、主庫B（192.168.159.149） 建立同步使用者

mysql> grant replication slave on *.* to 'repl'@'192.168.159.148' identified by '123456';
mysql> flush privileges;

5、 主庫A（192.168.159.148 ） 配置同步資訊
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                   |
+------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
| mysql-bin.000003 |     2552 |              |                  | 7b0fba4f-5cd4-11e8-bada-000c29ba59e8:1-20,
a1788b59-5d6a-11e8-bead-000c295d547a:3-9 |
+------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> change master to master_host='192.168.159.149',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=1727;

mysql > start slave;

mysql> show slave status \G;

注意看其中的這兩個狀態是YES就是正常

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

6、 主庫A（192.168.159.149 ） 配置同步資訊
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                         |
+------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------+
| mysql-bin.000004 |     1727 |              |                  | 7b0fba4f-5cd4-11e8-bada-000c29ba59e8:8-10:16-20,
a1788b59-5d6a-11e8-bead-000c295d547a:1-9 |
+------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

7、檢測主主同步
可以在A庫建立一個database 然後在B庫看是否同步，再去B庫建立一個database然後在A庫看是否同步。



注意事項：
mysql異常當機情況下，如果未設定sync_binlog=1或者innodb_flush_log_at_trx_commit=1很有可能出現binlog或者relaylog檔案出現損壞，導致主從不一致。

--未完待續




8、MYSQL啟動報錯

（1）

報錯現象：

[root@node1 mysql]# service mysql start

Starting MySQL.. ERROR! The server quit without updating PID file (/var/lib/mysql/node1.pid).

檢視報錯日誌：

[root@node1 mysql]# tail  /var/log/mysqld.log 

190512 19:59:10  InnoDB: Starting an apply batch of log records to the database...

InnoDB: Progress in percents: 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 

InnoDB: Apply batch completed

190512 19:59:10  InnoDB: Waiting for the background threads to start

190512 19:59:11 InnoDB: 5.5.40 started; log sequence number 1595675

190512 19:59:11 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306

190512 19:59:11 [Note]   - '0.0.0.0' resolves to '0.0.0.0';

190512 19:59:11 [Note] Server socket created on IP: '0.0.0.0'.

190512 19:59:11 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist

190512 19:59:11 mysqld_safe mysqld from pid file /var/lib/mysql/node1.pid ended

解決方法：

檢視配置檔案，發現配置檔案中datadir目錄是預設的，需要修改成自己設定的/usr/local/mysql/data/

[root@node1 mysql]# cat /etc/my.cnf 

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

[root@node1 mysql]# vi /etc/my.cnf 

datadir=/usr/local/mysql/data/

修改完成後重新啟動MYSQL成功

[root@node1 mysql]# service mysql start

Starting MySQL.. SUCCESS! 

（2）

報錯現象：

[root@node1 mysql]# /usr/local/mysql/bin/mysql -uroot

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

檢視配置檔案發現socket檔案在/var/lib/mysql/mysql.sock，而不是在 /tmp/mysql.sock

[root@node1 mysql]# cat /etc/my.cnf 

[mysqld]

datadir=/usr/local/mysql/data/

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

解決方法：
設定軟連結

ln -s /var/lib/mysql/mysql.sock  /tmp/mysql.sock

或者，修改配置檔案

注意一個問題，在生產環境，要注意時區問題

vi /etc/my.cnf

[mysqld]

default-time_zone = '+8:00'

使用北京時間的時區

生產環境還要注意連線數的設定（作業系統也要設定）

max_connections=3000

像以下這個範例就可以

[mysqld]

basedir=/XXX/ 自己填寫

datadir=/XXX/自己填寫

max_connections=3000

default-storage-engine=INNODB

character-set-server=utf8

slow_query_log_file = /XXX/ 自己填寫 /log/mysql-slow.log

slow_query_log=1

long_query_time = 5

log-queries-not-using-indexes = 1

bind-address=0.0.0.0

socket=/tmp/mysql.sock

user=mysql

default-time_zone = '+8:00'

#master

server-id=1

log-bin=mysql-bin

log-bin-index=mysql-bin.index

log-slave-updates=1

binlog-do-db=Master-slave

binlog-ignore-db=mysql

binlog_format=mixed

expire_logs_days=7

補充一下：

MYSQL還有一個和效能有關的關鍵引數，生產環境遇到的一個效能問題

innodb_flush_log_at_trx_commit

該引數控制重做日誌寫入磁碟的過程。我們知道 InnoDB 使用“Write Ahead Log”策略來避免資料丟失問題，即依靠重做日誌來保證資料能在丟失後進行恢復。因此，InnoDB 重做日誌的持久化非常重要。

該引數的有效值有 0、1、2：

0：事務提交時，不將重做日誌緩衝寫入磁碟，而是依靠 InnoDB 的主執行緒每秒執行一次重新整理到磁碟。因此如果 MySQL 發生當機，那麼就有可能丟失一部分事務。

1：事務提交時，會將重做日誌緩衝寫入磁碟，並且立即重新整理（fsync()）。注意，因為作業系統的“延遲寫”特性，此時的刷入只是寫到了作業系統的緩衝區中，因此執行同步操作才能保證一定持久化到了硬碟中。

2：事務提交時，會將重做日誌緩衝寫入磁碟，但是不會立即進行重新整理操作，因此只是寫到了作業系統的緩衝區。此時若作業系統發生當機而沒有即使的同步，也可能會丟失一部分資料。

可以看到，只有1才能真正地保證事務的永續性，但是由於重新整理操作 fsync() 是阻塞的，直到完成後才返回，我們知道寫磁碟的速度是很慢的，因此 MySQL 的效能會明顯地下降。如果不在乎事務丟失，，0和2能獲得更高的效能。

sync_binlog

該引數控制著二進位制日誌寫入磁碟的過程。

該引數的有效值為0 、1、N：

0：預設值。事務提交後，將二進位制日誌從緩衝寫入磁碟，但是不進行重新整理操作（fsync()），此時只是寫入了作業系統緩衝，若作業系統當機則會丟失部分二進位制日誌。

1：事務提交後，將二進位制檔案寫入磁碟並立即執行重新整理操作，相當於是同步寫入磁碟，不經過作業系統的快取。

N：每寫N次作業系統緩衝就執行一次重新整理操作。

二進位制日誌檔案涉及到資料的恢復，以及想在主從之間獲得最大的一致性，那麼應該將該引數設定為1，但同時也會造成一定的效能損耗。

通常，會將這兩個引數都設定成1來保證資料的安全，但是如果在某些情況下效能更重要，那麼可以考慮將其設為其他值來獲得最大的效能。

設定為1，最安全，但是有較大的效能損失；

設定為0，效率最高，但是最不安全；

設定為2，只要作業系統不崩潰，資料不會丟失；

一般有主從架構可設定為（主主架構也可以，或者對資料安全要求不高），這樣設定的好處是提高IO效能，面對大併發也有一定的效能提升

innodb_flush_log_at_trx_commit=2

sync_binlog=0