服务器说明:
MySQL-Master:192.168.1.142
MySQL-Slave1:192.168.1.136
MySQL-Slave2:192.168.1.140
关闭防火墙，关闭selinux
统一采用源码安装MySQL-5.6.16.tar.gz。具体的包可以百度或者官网下载(https://www.mysql.com/)
首先，在三台服务器上统一安装MySQL。具体过程如下
yum -y install make gcc-c++ cmake bison-devel ncurses-devel
tar -zvxf mysql-5.6.16.tar.gz
cd mysql-5.6.16
进行cmake编译
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=/usr/local/mysql/mysql.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
安装
make && make install
使用下面的命令查看是否有mysql用户及用户组
cat /etc/passwd #查看用户列表
cat /etc/group #查看用户组列表
如果没有就创建
groupadd mysql
useradd -g mysql mysql
修改/usr/local/mysql权限
chown -R mysql:mysql /usr/local/mysql
初始化MySQL
cd /usr/local/mysql #进入MySQL目录
scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql
###################################################################################################
注：在启动MySQL服务时，会按照一定次序搜索my.cnf，先在/etc目录下找，找不到则会搜索"$basedir/my.cnf"，在本例中就是 /usr/local/mysql/my.cnf，这是新版MySQL的配置文件的默认位置！
注意：在CentOS 6.4版操作系统的最小安装完成后，在/etc目录下会存在一个my.cnf，需要将此文件更名为其他的名字，如：/etc/my.cnf.bak，否则，该文件会干扰源码安装的MySQL的正确配置，造成无法启动
在使用"yum update"更新系统后，需要检查下/etc目录下是否会多出一个my.cnf，如果多出，将它重命名成别的。否则，MySQL将使用这个配置文件启动，可能造成无法正常启动等问题
##################################################################################################
#注意：
初始化时可能会遇到报错，报错原因如下
scripts/mysql_install_db -bash: scripts/mysql_install_db: /usr/bin/perl: 坏的解释器: 没有那个文件或目录
解决办法：
yum install -y perl-Module-Install.noarch
重新执行初始化
cp support-files/mysql.server /etc/init.d/mysql #拷贝启动脚本
chkconfig mysql on #添加开机自启
service mysql start --启动MySQL #启动MySQL
再启动过程中，发现启动失败一直报这个错误
Starting MySQL. ERROR! The server quit without updating PID file (/var/lib/mysql/xxxxxxxx.pid).
解决办法还是看我上面红框中的最后一句话，由于我的/etc/下有一个my.cnf 没有重命名，启动MySQL的时候会首先查看/etc，那么就会照成冲突，所以将/etc/my.cnf 备份为 my.cnf.bak。重新启动MySQL ok
MySQL启动成功后，root默认没有密码，我们需要设置root密码。
设置之前，我们需要先设置PATH，要不不能直接调用mysql
修改/etc/profile文件，在文件末尾添加
PATH=/usr/local/mysql/bin:$PATH
export PATH
退出后执行source /etc/profile
#登录mysql并修改密码
mysql
mysql> SET PASSWORD = PASSWORD('new password');
到这里MySQL就算是搭建完成了，接下来配置主从同步
首先创建授权账户
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'192.168.1.136' IDENTIFIED BY 'slave1';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave2'@'192.168.1.140' IDENTIFIED BY 'slave2';
Master数据库退出MySQL，编辑/usr/local/mysql/my.cnf
[mysqld] #在mysqld下面添加如下两行
server-id = 2
log-bin=mysql-bin
binlog-ignore-db=mysql #忽略的数据库
binlog-ignore-db=information-schema
重启Master数据库
查看状态
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000003 | 120 | | information_schema,mysql | |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
同样将两台slave服务器也进行如下修改
编辑/usr/local/mysql/my.cnf文件
#slave1
[mysqld]
log-bin=mysql-bin
server-id=136
replicate_ignore_db=mysql #被忽略的数据库
replicate-ignore-db=information-schema
slave-skip-errors=all #跳过所有错误
#slave2
[mysqld]
log-bin=mysql-bin
server-id=140
replicate_ignore_db=mysql #被忽略的数据库
replicate-ignore-db=information-schema
slave-skip-errors=all #跳过所有错误
参数说明
replicate_do_db=db_name #只复制db_name数据库
replicate_ignore_db=db_name #不复制db_name数据库
replicate_do_table=tb_name #复制tb_name表
replicate_ignore_table=tb_name #不复制tb_name表
replicate_wild_do_table=test% #复制以test为开头并且后面跟上任意
read-only=1 #只读
重新启动两台从库
分别在两台机器上操作stop slave；
然后在分别在两台机器上做如下操作
在slave1上操作
change master to master_host='192.168.1.142',
master_user='slave1', #注意用户名
master_password='slave1', #注意密码
master_log_file='mysql-bin.000003',
master_log_pos=120;
在slave2上操作
change master to master_host='192.168.1.142',
master_user='slave2', #注意用户名
master_password='slave2', #注意面
master_log_file='mysql-bin.000003',
master_log_pos=120;
分别在两台机器上操作start slave;
最后分别查看从库状态
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.142
Master_User: slave1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 120
Relay_Log_File: elk-node2-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,information-schema
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: 120
Relay_Log_Space: 460
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: 2
Master_UUID: 780d388c-7040-11e7-b42a-000c29199d74
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.142
Master_User: slave2
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 120
Relay_Log_File: controller01-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,information-schema
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: 120
Relay_Log_Space: 463
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: 2
Master_UUID: 780d388c-7040-11e7-b42a-000c29199d74
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
通过主库查看从库状态
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 136 | | 3306 | 2 | 8346c426-7040-11e7-b42a-000c29dcd50c |
| 140 | | 3306 | 2 | 8d82f208-7040-11e7-b42a-000c29e2e25f |
+-----------+------+------+-----------+--------------------------------------+
2 rows in set (0.00 sec)
ps:可能执行show slave status;时发现 Slave_IO_Running: Connecting。解决方法，首先查看用户名密码是否正确，各主机之间是否能ping同，防火墙是否关闭，博主在做实验的时候就没有关闭防火墙导致一直连接不上。
测试
在Master数据库创建一个测试库与测试表
/*创建库*/
mysql> CREATE database test_mysql;
/*创建表*/
mysql> create table python_one(
-> id int(11)
-> name varchar(255)
-> );
/*插入两条数据*/
INSERT INTO python_one(id,name,) value(1,'字典');
INSERT INTO python_one(id,name,) value(2,'函数');
/*查看数据*/
mysql> SELECT * FROM python_one;
+------+--------+
| id | name |
+------+--------+
| 1 | 字典 |
| 2 | 函数 |
+------+--------+
2 rows in set (0.01 sec)
分别在两个从库上面查看数据
Slave1
mysql> SELECT * FROM test_mysql.python_one;
+------+--------+
| id | name |
+------+--------+
| 1 | 字典 |
| 2 | 函数 |
+------+--------+
2 rows in set (0.00 sec)
Slave2
mysql> SELECT * FROM test_mysql.python_one;
+------+--------+
| id | name |
+------+--------+
| 1 | 字典 |
| 2 | 函数 |
+------+--------+
2 rows in set (0.00 sec)
测试结果没有问题，一主多从就搭建完成了