mysql 集群 运维_Linux系统运维之MYSQL数据库集群部署(主从复制)

一、介绍

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= 3306socket= /data/opt/mysql/3306/mysql.sock

#The MySQL Server

[mysqld]

server-id=209innodb_flush_log_at_trx_commit=1sync_binlog=1port= 3306user=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= 10240lower_case_table_names=1explicit_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= 1slow_query_log_file= /data/opt/mysql/binlog/slow_query.log

general_log= 0general_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= 3innodb_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=1sync_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.000007Relay_Master_Log_File: mysql-bin.000007Slave_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/boostmake -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