# ​Mysql双主配置及安装部署

> 作者/来源: UCloud 运营管理员
> 发布时间: 2023-01-11T05:20:00.000Z
> 分类: CDN
> 标签: AI, 数据库, CDN
> 原文链接: http://117.50.162.249:3000/yun/articles/915

---

# ​Mysql双主配置及安装部署

> 来源: https://www.ucloud.cn/yun/129973.html
> 作者: IT那活儿
> 发布日期: 发布于2023-01-11 13:20

​Mysql双主配置及安装部署

亲爱滴伙伴们,今天给大家分享一下Mysql的安装以及双主复制的部署，Mysql的双主配置在生产环境中也是常用到的哦！

[概述]

近期由于在工作中客户要求安装Mysql并进行双主配置，下面我来介绍一下Mysql的安装与双主的配置哦，有不对的地方，欢迎大家指出哦！！

[安装]

1）获取安装介质

https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.23-linux-glibc2.12-x86\_64.tar.xz

2)创建用户

groupaddmysql

useradd-r -g mysql -s /bin/false mysql

3)解压安装包

cd/usr/local

chown-R mysql:mysql  mysql-8.0.23-linux-glibc2.12-x86\_64.tar.xz

tar-xvf mysql-8.0.23-linux-glibc2.12-x86\_64.tar.xz

chown-R mysql:mysql mysql-8.0.23-linux-glibc2.12-x86\_64

ln-s   mysql-8.0.23-linux-glibc2.12-x86\_64 mysql

chown-R mysql:mysql mysql

4）创建数据文件目录：

mkdir-p /mysqldata/data

mkdir-p /mysqldata/log

mkdir-p /mysqldata/sys

chown-R mysql:mysql  /mysqldata/

chmod-R 750 /mysqldata/

5）配置环境变量(写到/etc/profile)

exportPATH=/usr/local/mysql/bin:$PATH

source/etc/profile

6）编辑参数文件

主库：

vi/etc/my.cnf

[mysql]

prompt=[u@h][d]>\\_

socket=/mysqldata/sys/mysql.sock

[mysqld]

user=mysql

datadir=/mysqldata/data/

socket=/mysqldata/sys/mysql.sock

character\_set\_server=utf8mb4

transaction\_isolation=read-committed

explicit\_defaults\_for\_timestamp=1

max\_allowed\_packet=16777216

event\_scheduler=1

server\_id=212211

lower\_case\_table\_names=1

interactive\_timeout=1800

wait\_timeout=1800

skip\_name\_resolve=1

max\_connections=2000

max\_connect\_errors=100000

table\_open\_cache=4096

table\_definition\_cache=4096

table\_open\_cache\_instances=64

read\_buffer\_size=6M

read\_rnd\_buffer\_size=16M

sort\_buffer\_size=16M

tmp\_table\_size=64M

join\_buffer\_size=64M

thread\_cache\_size=64

log\_error=/mysqldata/log/alert.log

log\_bin=/mysqldata/log/binlog

master\_info\_repository=table

relay\_log\_info\_repository=table

sync\_binlog=1

gtid\_mode=on

enforce\_gtid\_consistency=1

log\_slave\_updates=1

binlog\_format=row

binlog\_rows\_query\_log\_events=1

relay\_log=/mysqldata/log/relay.log

relay\_log\_recovery=1

#slave\_rows\_search\_algorithms=index\_scan,hash\_scan

innodb\_buffer\_pool\_size=16G

innodb\_buffer\_pool\_instances=4

innodb\_buffer\_pool\_load\_at\_startup=1

innodb\_buffer\_pool\_dump\_at\_shutdown=1

innodb\_lru\_scan\_depth=2048

innodb\_flush\_method=o\_direct

innodb\_open\_files=4096

innodb\_log\_file\_size=1024000000

从库：

从库的参数基本上跟主库的参数一致，只需要改变一个server\_id，这个配置双主的时候不能一样。

7）初始化数据库

cd/usr/local/mysql

bin/mysqld--initialize  --lower-case-table-names=1 -user=mysql

8）启动数据库

cd/usr/local/mysql

bin/mysqld\_safe--user=mysql &

9）进入mysql修改密码

mysql-uroot -p（初始密码在error.log里）

VKgfkfqUA0,7

alteruser root@localhost identified by My1qaz@WSX;

flushprivileges;

10）添加服务到系统

cd/usr/local/mysql

cpsupport-files/mysql.server /etc/init.d/mysql

chmod+x /etc/init.d/mysql

chkconfig--add mysql

chkconfig--list mysql

11）测试

servicemysql start

[双主配置]

**1、192.168.245.138->192.168.245.139方向**

1）创建复制用户（主库：192.168.245.138）

createuser repl@% identified with mysql\_native\_password by "2w3e@W#E";

grantfile on \*.\* to repl@%;

grantreplication slave on \*.\* to repl@%;

flushprivileges;

2）开启复制进程（从库：192.168.245.139）

changemaster tomaster\_host=192.168.245.138,master\_port=3306,master\_user=repl,master\_password=2w3e@W#E,master\_auto\_position=1;

startslave;

3）检查slave状态

showslave statusG

**2、192.168.245.139->192.168.245.138方向**

1）创建复制用户（主库：192.168.245.139）

createuser sysadmin@% identified  with mysql\_native\_password by"1q2w!Q@W";

grantall on \*.\* to sysadmin@%;

flushprivileges;

2）开启复制进程（从库：192.168.245.138）

changemaster tomaster\_host=192.168.245.139,master\_port=3306,master\_user=repl,master\_password=2w3e@W#E,master\_auto\_position=1;

startslave;

3）检查slave状态

showslave statusG

[测试]

1）192.168.245.138->192.168.245.139方向

192.168.245.138：

CREATEDATABASE `gohealth-plat` CHARACTER SET utf8mb4 COLLATEutf8mb4\_general\_ci;

usegohealth-plat;

createtable product(

product\_idint(10) not NULL,

product\_namevarchar(100) not NULL,

product\_tyepvarchar(32) not NULL,

sale\_priceint(10) default 0,

input\_priceint(10) default 0,

regist\_timedate,

primarykey (product\_id));

192.168.245.139：

showdatabases;

usegohealth-plat;

showtables;

2）192.168.245.139->192.168.245.138方向

192.168.245.139：

usegohealth-plat;

insertinto  product values(555,sdfsd,sdfd,54,215,null);

select\* from product;

192.168.245.138：

select\* from product;

[结论]

MySQL软件安装以及双主配置还是非常简单的,如果在启动复制过程中报错，可以使用showslave status进行查看是什么原因导致的，好了，今天的分享就到此结束了哦！！

![](https://ucloud-blog.cn-bj.ufileos.com/articles/129973/images/129973_000.png)

END

![](https://ucloud-blog.cn-bj.ufileos.com/articles/129973/images/129973_001.png)