Mysql 主从配置部署方案详细文档

Mysql 主从配置部署方案详细文档

一、Docker安装MySQL服务

1、基于MySQL镜像创建docker容器

docker run --nethost --name mysql -e TZ=Asia/Shanghai -e MYSQL_ROOT_PASSWORD=x#20190531 -d mysql-w

3、查看容器运行情况

# docker ps

4、进入容器

# docker exec -it mysql /bin/bash

5、安装vim

# apt-get update

# apt-get install vim

二修改mysql主从配置文件

执行#docker exec -it mysql /bin/bash进入数据库容器后编辑mysqld.cnf配置添加如下配置，配置修改后退出容器执行#docker restart mysql重新启动。

1、配置文件【主】vi /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]

pid-file = /var/run/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.sock

datadir = /var/lib/mysql

lower_case_table_names=1

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

character_set_server=utf8

init_connect='SET NAMES utf8'

max_connections=10000

max_user_connections=1000

log-bin=mysql-bin

server-id=1

binlog-ignore-db=information_schema

binlog-ignore-db=cluster

binlog-ignore-db=mysql

2、配置文件【从】vi /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]

pid-file = /var/run/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.sock

datadir = /var/lib/mysql

lower_case_table_names=1

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

character_set_server=utf8

init_connect='SET NAMES utf8'

max_connections=10000

max_user_connections=1000

log-bin=mysql-bin

server-id=168

binlog-ignore-db=information_schema

binlog-ignore-db=cluster

binlog-ignore-db=mysql

replicate-ignore-db=mysql

log-slave-updates

slave-skip-errors=all

slave-net-timeout=60

三、设定授权主从数据库账号

1、【主节点】授权复制权限账号给从节点

grant replication client,replication slave on *.* to 'tfrepuser'@'%' identified by 'tfrepuser110';

--replication slave 用于建立复制时需要用到的用户权限，slave server必须被master server授权具有该权限的用户，才能通过该用户复制。

--replication client该权限可以使用如"SHOW SLAVE STATUS"、"SHOW MASTER STATUS"等命令。

flush privileges; --刷新MySQL的系统权限相关表

show master status;-- 查看状态

（记住bin-master-log文件以及序号）

2、【从节点】指定主节点，复制账号

change master to master_host='192.168.0.121', master_port=3306,master_user='tfrepuser1',master_password='tfrepuser110',master_log_file='mysql-bin.000004',master_log_pos=606;

--master_host='192.168.0.121' 指定主节点IP

--master_port=3305指定主节点端口

--master_user='tfrepuser' 指定被授权用户

--master_password='tfrepuser110' 指定主节点密码

--master_log_file='master-bin.000003' 指定io线程读取主库的二进制文件

--master_log_pos=614 指定序号

start slave;--打开主从同步

show slave status\G--查看状态

mysql> show slave status\G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 120.55.1.212

                  Master_User: tfrepuser1

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000002

          Read_Master_Log_Pos: 26279

               Relay_Log_File: 97d566e7963c-relay-bin.000004

                Relay_Log_Pos: 20855

        Relay_Master_Log_File: mysql-bin.000002

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

注意：必须

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

四、测试主从设置

新建数据库进行操作查看：

【主】对主库创建数据库表进行增删改操作;

创建测试数据库zerow：create database zerow;

新建测试表users：create table `users` (`id` int PRIMARY KEY,`name` varchar(255) NOT NULL,`mobile` varchar(255) NULL DEFAULT '0');

插入数据：insert into users (id,name,mobile) values (1, '曹操',110120);

修改数据：update users set mobile=110120999 where id=1;

删除数据：delete from users where name='曹操';

删除测试表：drop table users;

删除测试库：drop database zerow;

【从】同步查询丛库是否同步备份

mysql> show databases;--查看数据库

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

4 rows in set (0.00 sec)

 

mysql> show databases;--新建库后再查看数据库

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

| zerow              |

+--------------------+

5 rows in set (0.00 sec)

 

mysql> use zerow;--使用数据库zerow

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> show tables;--查看表

+-----------------+

| Tables_in_zerow |

+-----------------+

| users           |

+-----------------+

1 row in set (0.00 sec)

 

mysql> select * from users;--查看表里数据

Empty set (0.00 sec)

 

mysql> select * from users;--插入数据后再查看表里数据

+----+------+--------+

| id | name | mobile |

+----+------+--------+

|  1 | ??   | 110120 |

+----+------+--------+

1 row in set (0.00 sec)

 

mysql> select * from users;--修改数据后再查看表里数据

+----+------+-----------+

| id | name | mobile    |

+----+------+-----------+

|  1 | ??   | 110120999 |

+----+------+-----------+

1 row in set (0.00 sec)

 

mysql> select * from users;--删除数据后再查看表里数据

Empty set (0.00 sec)

 

mysql> show tables;--删除表后再查看表

Empty set (0.00 sec)

 

mysql> show databases;--删除数据库后再查看数据库

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

4 rows in set (0.00 sec)

 

mysql>