快速使用docker 搭建 MySQL主从复制与读写分离

搭建 MySQL主从复制与读写分离：

在实际环境中，如果对数据库的读和写都在同一个数据库服务中操作，无论实在安全性、高可用性，
还是高并发等各个方面都是完全不能满足实际需求的，因此，一般来说都只通过主从复制的方式来同

本机已经搭建了一个mysql，不知道如何构建可以参考docker 搭建mysql

1，配置主MySQL 配置文件：

vim /etc/my.cnf        #在配置文件添加

server-id = 1              #修改
log-bin=master-bin         #开启二进制日志
log-slave-updates=true     #开启从服务器同步主服务器二进制日志

2，重启mysql，进入数据库给从服务器授权 :

[root@localhost /]# docker exec -it mysql bash                   //进入主mysql容器

root@d89e02feef14:/# mysql -u root  -proot
mysql> create user `coolr`@`%` identified by "root";             #创建用户
mysql> grant all on *.* to `coolr`@`%` with grant option;        #添加权限

mysql> FLUSH PRIVILEGES;                                         #刷新权限
mysql> show master status;                                       #查看主服务器状态

如图：说明我们的bin-log 日志已经开启了

3，从机搭建 :


[root@localhost mysql]# mkdir mysql-serve1
#创建 从库映射目录
[root@localhost mysql]# cd mysql-serve1

[root@localhost mysql-serve1]# cp /etc/my.cnf /docker/mysql/mysql-serve1/
#复制主库my.cnf配置
[root@localhost mysql-serve1]# vim my.cnf
#修改从库my.cnf配置
[client]
port        = 3307          //从库端口
socket        = /var/mysql/mysql.sock

[mysqld]
secure_file_priv=/usr/local/mysql/data

datadir = /usr/local/mysql/data

default_storage_engine = InnoDB
performance_schema_max_table_instances = 400
table_definition_cache = 400
skip-external-locking
key_buffer_size = 32M
max_allowed_packet = 100G
table_open_cache = 128
sort_buffer_size = 768K
net_buffer_length = 4K
read_buffer_size = 768K
read_rnd_buffer_size = 256K
myisam_sort_buffer_size = 8M
thread_cache_size = 16
tmp_table_size = 32M
default_authentication_plugin = mysql_native_password
lower_case_table_names = 1
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

explicit_defaults_for_timestamp = true
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535

log-bin=mysql-bin
binlog_format=mixed
server-id = 2                # id 主库不一样  别一台从服务器同样配置 id 不同即可
binlog_expire_logs_seconds = 600000
slow_query_log=1
slow-query-log-file=/usr/local/mysql/data/mysql-slow.log
long_query_time=3
early-plugin-load = ""

innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data
innodb_buffer_pool_size = 128M
innodb_log_file_size = 64M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_max_dirty_pages_pct = 90
innodb_read_io_threads = 1
innodb_write_io_threads = 1

[mysqldump]
quick
max_allowed_packet = 500M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 32M
sort_buffer_size = 768K
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

修改完成之后构建从机的mysql

3，从机搭建 :

[root@localhost mysql-serve1]# docker run -p 3307:3307 -d --name mysql-serve1 -v /docker/mysql/mysql-serve1/my.cnf:/etc/mysql/my.cnf --privileged=true -e MYSQL_ROOT_PASSWORD=root mysql
#构建从库
[root@localhost mysql-serve1]# docker exec -it mysql-serve1 bash
#进入从库
root@cacb4a88b156:/# mysql -uroot -proot

mysql> change master to master_host='192.168.74.129',master_user='coolr',master_password='root',master_log_file='mysql-bin.000011',master_log_pos=983460;   # 主服务器地址、授权用户、二进制日志名称、偏移量 。
mysql> start slave;
# 开启从服务器
mysql> show slave status\G;
 #查看从服务器状态
*************************** 1. row ***************************
           Slave_IO_State: Waiting for master to send event
              Master_Host: 192.168.217.130
              Master_User: myslave
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: master-bin.000001
      Read_Master_Log_Pos: 604
           Relay_Log_File: relay-log-bin.000002
            Relay_Log_Pos: 321
    Relay_Master_Log_File: master-bin.000001
         Slave_IO_Running: Yes                # 为yes 表示同步中
        Slave_SQL_Running: Yes                # 为yes 表示同步中