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

灰太狼 2021-10-23 22:22 468阅读 0赞

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>

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

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 mysql主从同步配置超详细

相关 MySQL 部署主从数据库

相关 MySql数据库主从配置详细教程

相关 Mysql的高可用方案及主从详细配置

相关 MySQL主从配置(详细O(∩_∩)O)

相关 MySQL主从同步配置（详细图解）

相关多机部署之MySQL主从配置

相关 Elasticsearch部署文档详细

相关 MySQL5.7配置主从同步详细步骤

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