前言：
前面那篇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源码包
https://downloads.mysql.com/archives/get/file/mysql-5.6.40.tar.gz
4、解压tar -zxvf mysql-5.6.40.tar.gz

cd /opt/

tar -zxvf mysql-5.6.40.tar.gz

cdmysql-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]

server-id=101default-storage-engine=InnoDBlower_case_table_names=1log-bin=/usr/local/mysql/log/mysql-bin.loglog-bin-index=/usr/local/mysql/log/mysql-bin.indexexpire_logs_days=30datadir=/usr/local/mysql/datasocket=/tmp/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0[mysqld_safe]log-error=/usr/local/mysql/data/mysqld.logpid-file=/usr/local/mysql/mysqld.pid

说明：

log-bin：给出二进制日志的所有文件基础名

log-bin-index：给出二进制日志文件的文件名，通常以000001开始，顺序递增。全名：master-bin.000001

server-id：mysql服务器唯一ID，在主从复制的所有服务器中必须唯一。

启动数据库
传统启动方式
/usr/local/mysql/bin/mysqld_safe --user=mysql &
制作成服务启动
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
查看启动是否成功
netstat -tnl|grep 3306
ps -ef|grep mysql
相关命令
service mysql start
停止mysql服务
service mysql stop
重启mysql服务
service mysql restart
添加到开机启动项
chkconfig --add mysql

b、创建用户，并赋予权限：登陆数据库时报错
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysqld.sock' (2)

ln -s /tmp/mysql.sock /tmp/mysqld.sock

登陆
mysql -uroot

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端配置部署a、配置参数：[mysqld]

vi /etc/my.cnf

[mysqld]server-id=102default-storage-engine=InnoDBlower_case_table_names=1log-bin=/usr/local/mysql/log/mysql-bin.loglog-bin-index=/usr/local/mysql/log/mysql-bin.indexexpire_logs_days=30datadir=/usr/local/mysql/datasocket=/tmp/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0
[mysqld_safe]log-error=/usr/local/mysql/data/mysqld.logpid-file=/usr/local/mysql/mysqld.pid

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、从库与主机建立同步

以下为建立主从同步最基本的6个项：change master to

master_host='xxx.xxx.xxx.xxx', #主库IP

master_port=6001, #主库mysqld的端口

master_user='repl', #主库中创建的有REPLICATION SLAVE权限的用户

master_password='xxxxxxxx', #该用户的密码

master_log_file='mysql-bin.000xxx', #已在导入时指定了

master_log_pos=xxxxxx; #已在导入时指定了

start slave;

例子：

master_log_file和master_log_pos通过在主库上使用命令获得：

show master status \G;

在从库上执行：

change master to

master_host='192.168.159.148',

master_port=3306,

master_user='repl_user',

master_password='123456',

master_log_file='mysql-bin.000002',

master_log_pos=415;

start slave;

验证主从是否搭建成功在从库执行
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-urootpassword

mysql>usemysql;

mysql>descuser;

mysql>GRANTALLPRIVILEGESON*.*TOroot@"%"IDENTIFIEDBY"root";//授权远程连接

mysql>updateusersetPassword=password('123456')whereUser='root';//设置root用户密码

mysql>selectHost,User,PasswordfromuserwhereUser='root';

mysql>flushprivileges;

mysql>exit

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

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

[client]port=3306socket=/tmp/mysql.sock[mysqld]basedir=/usr/local/mysqlport=3306socket=/tmp/mysql.sockdatadir=/usr/local/mysql/datapid-file=/usr/local/mysql/data/mysql.pidlog-error=/usr/local/mysql/data/mysql.errserver-id=1auto_increment_offset=1auto_increment_increment=2#奇数IDlog-bin=mysql-bin#打开二进制功能,MASTER主服务器必须打开此项binlog-format=ROW#binlog-row-p_w_picpath=minimal#这个参数不知道为何会报错log-slave-updates=truegtid-mode=onenforce-gtid-consistency=truemaster-info-repository=TABLErelay-log-info-repository=TABLEsync-master-info=1slave-parallel-workers=0sync_binlog=0binlog-checksum=CRC32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events=1#expire_logs_days=5max_binlog_size=1024M#binlog单文件最大值replicate-ignore-db=mysql#忽略不同步主从的数据库replicate-ignore-db=information_schemareplicate-ignore-db=performance_schemareplicate-ignore-db=testreplicate-ignore-db=zabbixmax_connections=3000max_connect_errors=30skip-character-set-client-handshake#忽略应用程序想要设置的其他字符集init-connect='SETNAMESutf8'#连接时执行的SQLcharacter-set-server=utf8#服务端默认字符集wait_timeout=1800#请求的最大连接时间interactive_timeout=1800#和上一参数同时修改才会生效sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES#sql模式max_allowed_packet=10Mbulk_insert_buffer_size=8Mquery_cache_type=1query_cache_size=128Mquery_cache_limit=4Mkey_buffer_size=256Mread_buffer_size=16Kskip-name-resolveslow_query_log=1long_query_time=6slow_query_log_file=slow-query.loginnodb_flush_log_at_trx_commit=2innodb_log_buffer_size=16M[mysql]no-auto-rehash[myisamchk]key_buffer_size=20Msort_buffer_size=20Mread_buffer=2Mwrite_buffer=2M[mysqlhotcopy]interactive-timeout[mysqldump]quickmax_allowed_packet=16M[mysqld_safe]

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

[client]port=3306socket=/tmp/mysql.sock[mysqld]basedir=/usr/local/mysqlport=3306socket=/tmp/mysql.sockdatadir=/usr/local/mysql/datapid-file=/usr/local/mysql/data/mysql.pidlog-error=/usr/local/mysql/data/mysql.errserver-id=2auto_increment_offset=2auto_increment_increment=2#偶数IDlog-bin=mysql-bin#打开二进制功能,MASTER主服务器必须打开此项binlog-format=ROW#binlog-row-p_w_picpath=minimal#这个参数不知道为何会报错log-slave-updates=truegtid-mode=onenforce-gtid-consistency=truemaster-info-repository=TABLErelay-log-info-repository=TABLEsync-master-info=1slave-parallel-workers=0sync_binlog=0binlog-checksum=CRC32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events=1#expire_logs_days=5max_binlog_size=1024M#binlog单文件最大值replicate-ignore-db=mysql#忽略不同步主从的数据库replicate-ignore-db=information_schemareplicate-ignore-db=performance_schemareplicate-ignore-db=testreplicate-ignore-db=zabbixmax_connections=3000max_connect_errors=30skip-character-set-client-handshake#忽略应用程序想要设置的其他字符集init-connect='SETNAMESutf8'#连接时执行的SQLcharacter-set-server=utf8#服务端默认字符集wait_timeout=1800#请求的最大连接时间interactive_timeout=1800#和上一参数同时修改才会生效sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES#sql模式max_allowed_packet=10Mbulk_insert_buffer_size=8Mquery_cache_type=1query_cache_size=128Mquery_cache_limit=4Mkey_buffer_size=256Mread_buffer_size=16Kskip-name-resolveslow_query_log=1long_query_time=6slow_query_log_file=slow-query.loginnodb_flush_log_at_trx_commit=2innodb_log_buffer_size=16M[mysql]no-auto-rehash[myisamchk]key_buffer_size=20Msort_buffer_size=20Mread_buffer=2Mwrite_buffer=2M[mysqlhotcopy]interactive-timeout[mysqldump]quickmax_allowed_packet=16M[mysqld_safe]

3、主库A（192.168.159.148）创建同步用户

mysql>grantreplicationslaveon*.*to'repl'@'192.168.159.149'identifiedby'123456';mysql>flushprivileges;

4、主库B（192.168.159.149）创建同步用户

mysql>grantreplicationslaveon*.*to'repl'@'192.168.159.148'identifiedby'123456';mysql>flushprivileges;

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>showslavestatus\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)

mysql>change master to master_host='192.168.159.148',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=2552;

mysql> start slave;

mysql> show slave status \G;
注意看其中的这两个状态是YES就是正常

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