一、介绍
Mysql主从复制，前段时间生产环境部署了一套主从复制的架构，当时现找了很多资料，现在记录下
二、拓扑图
三、环境以及软件版本
| 主机名 | IP | 操作系统 | 角色 | 软件版本 |
| MysqlDB_Master | 192.168.0.1 | CentOS release 7.1 | Master | Mysql 5.6.36 |
| MysqlDB_Slave | 192.168.0.2 | CentOS release 7.1 | Slave | Mysql 5.6.36 |
四、源码安装
之前都是yum直接安装的，这次尝试使用源码安装，安装包如下：
- mysql-5.6.36.tar.gz
- cmake-3.8.1.tar.gz
首先安装前提环境：
[root@MysqlDB_Master soft] yum groupinstall 'Development tools' -y [root@MysqlDB_Master soft] tar -xf cmake-3.8.1.tar.gz [root@MysqlDB_Master soft] cd cmake-3.8.1 [root@MysqlDB_Master cmake-3.8.1] ./bootstrap [root@MysqlDB_Master cmake-3.8.1] gmake && gmake install
安装mysql：
[root@MysqlDB_Master soft] tar -xf mysql-5.6.36.tar.gz [root@MysqlDB_Master soft] cd mysql-5.6.36 [root@MysqlDB_Master mysql-5.6.36] cmake -DCMAKE_INSTALL_PREFIX=/data/opt/mysql -DMYSQL_DATADIR=/data/opt/mysql/data -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=all -DENABLED_LOCAL_INFILE=1 [root@MysqlDB_Master mysql-5.6.36] make && make install
mysql配置相关：
[root@MysqlDB_Master mysql-5.6.36] mkdir -p /data/opt/mysql/{3306,binlog,tmp} [root@MysqlDB_Master mysql-5.6.36] chown -R mysql.mysql /data/opt/mysql [root@MysqlDB_Master mysql-5.6.36] cp support-files/my-default.cnf /etc/my.conf [root@MysqlDB_Master mysql-5.6.36] vim /etc/my.conf =================================== [client] port = 3306 socket = /data/opt/mysql/3306/mysql.sock #The MySQL Server [mysqld] server-id=209 innodb_flush_log_at_trx_commit=1 sync_binlog=1 port = 3306 user = mysql socket = /data/opt/mysql/3306/mysql.sock pid-file = /data/opt/mysql/3306/mysql.pid basedir = /data/opt/mysql datadir = /data/opt/mysql/data tmpdir = /data/opt/mysql/tmp open_files_limit = 10240 lower_case_table_names=1 explicit_defaults_for_timestamp sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #Buffer max_allowed_packet = 256M max_heap_table_size = 256M net_buffer_length = 8k sort_buffer_size = 2M join_buffer_size = 4M read_buffer_size = 2M read_rnd_buffer_size = 16M #Log log-bin = /data/opt/mysql/binlog/mysql-bin binlog_cache_size = 32M max_binlog_cache_size = 512M max_binlog_size = 512M binlog_format = mixed log_output = FILE log-error = /data/opt/mysql/binlog/mysql-error.log slow_query_log = 1 slow_query_log_file = /data/opt/mysql/binlog/slow_query.log general_log = 0 general_log_file = /data/opt/mysql/binlog/general_query.log expire-logs-days = 14 #InnoDB innodb_data_file_path = ibdata1:2048M:autoextend innodb_log_file_size = 256M innodb_log_files_in_group = 3 innodb_buffer_pool_size = 1024M [mysql] no-auto-rehash prompt = (u@h)[d]>\_ default-character-set = gbk =================================== #添加mysql环境变量 [root@MysqlDB_Master mysql-5.6.36] echo 'export PATH=$PATH:/data/opt/mysql/bin' >> /etc/profile && source /etc/profile #初始化数据库 [root@MysqlDB_Master mysql-5.6.36] cd /data/opt/mysql [root@MysqlDB_Master mysql] ./scripts/mysql_install_db --defaults-file=/etc/my.cnf --user=mysql [root@MysqlDB_Master mysql] ./bin/mysqld_safe --user=mysql & #添加service，并配置开机启动 [root@MysqlDB_Master mysql] cp /soft/mysql-5.6.36/support-files/mysql.server /etc/init.d/mysqld [root@MysqlDB_Master mysql] chmod +x /ect/init.d/mysqld
[root@MysqlDB_Master mysql] chkconfig --add mysqld
[root@MysqlDB_Master mysql] chkconfig mysqld on
优化mysql默认配置，配置访问权限：
[root@MysqlDB_Master ~] mysql -uroot -p #清空默认数据 (root@localhost)[(none)] > select * from mysql.db G (root@localhost)[(none)] > truncate table mysql.db; (root@localhost)[(none)] > flush privileges; (root@localhost)[(none)] > select * from mysql.db G #配置访问权限 (root@localhost)[(none)] > use mysql; (root@localhost)[(none)] > desc user; (root@localhost)[(none)] > grant all privileges on *.* to root@"%" identified by "root"; (root@localhost)[(none)] > update user set Password=password('XXXXXX') where User='root'; (root@localhost)[(none)] > select Host,User,Password from user where User='root'; (root@localhost)[(none)] > flush privileges; (root@localhost)[(none)] > exit;
以上，Mysql源码安装完成，在MysqlDB_Slave服务器上进行相同配置即可。
五、配置主从
关于主从的配置，之前在配置文件里已经填写，“server-id”值主从不同，从值大于主值，主要配置如下：
[mysqld] server-id=209 #InnoDB innodb_flush_log_at_trx_commit=1 sync_binlog=1
连接到主库，配置同步账号并授权从服务器连接：
(root@localhost)[(none)] grant replication slave,reload,super on *.* to 'repl'@'192.168.0.2' identified by 'backup'; (root@localhost)[(none)] flush tables with read lock; (root@localhost)[(none)] show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000007 | 321| | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
连接到从库，配置主服务器IP以及同步账号等信息：
(root@localhost)[(none)] > change master to master_host='192.168.0.1',master_user='repl',master_password='backup',master_log_file='mysql-bin.000007',master_log_pos=321; (root@localhost)[(none)] > start slave; (root@localhost)[(none)] > show slave status G
确认主从节点同步正常，主要确认以下值：
Master_Log_File: mysql-bin.000007 Relay_Master_Log_File: mysql-bin.000007 Slave_IO_Running: Yes Slave_SQL_Running: Yes
连接到主库，解除锁，并创建新DB：
(root@localhost)[(none)] unlock tables;
(root@localhost)[(none)] create database testDB;
连接到从库，查看新建DB是否同步过来：
(root@localhost)[(none)] show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| testDB |
+--------------------+
以上，Mysql集群，主从同步配置完成。
补充：
1.Mysql5.7安装需要引入boost库
tar -xf boost_1_59_0.tar.gz -C /usr/local/ cd /usr/local/ ln -s boost_1_59_0 boots cmake -DCMAKE_INSTALL_PREFIX=/data/opt/mysql -DMYSQL_DATADIR=/data/opt/mysql/data -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=all -DENABLED_LOCAL_INFILE=1 -DENABLE_DOWNLOADS=1 -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/boost make -j `grep processor /proc/cpuinfo | wc -l` && make install
2.Mysql5.7密码字段改变
5.6是Password,5.7是authentication_string
3.Mysql5.7修改密码
update user set authentication_string=password('XXXXXX') where User='root';
4.Mysql5.7初始化
data目录不需要手动建立：
./bin/mysqld --initialize-insecure --user=mysql --basedir=/data/opt/mysql --datadir=/data/opt/mysql/data
5.Mysql5.7默认日志时间是UTC
在配置文件中[mysqld]段落中添加log_timestamps=SYSTEM