​Mysql双主配置及安装部署

安装Mysql

1）获取安装介质

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

2）创建用户

groupadd mysql

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)

export PATH=/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 -u root -p（初始密码在error.log里）

VKgfkfqUA0,7

alter user root@localhost identified by My1qaz@WSX;

flush privileges;

10）添加服务到系统

cd /usr/local/mysql/cp support-files/mysql.server /etc/init.d/mysql

chmod +x /etc/init.d/mysql

chkconfig –add mysql

chkconfig –list mysql

11）测试service mysql start

双主复制配置

1、192.168.245.138->192.168.245.139方向

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

create user repl@’%’ identified with mysql_native_password by “2w3e@W#E”;

grant file on *.* to repl@’%’;

grant replication slave on *.* to repl@’%’;

flush privileges;

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

change master to master_host=192.168.245.138, master_port=3306, master_user=repl, master_password=2w3e@W#E, master_auto_position=1;

start slave;

3）检查slave状态

show slave statusG

2、192.168.245.139->192.168.245.138方向

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

create user sysadmin@’%’ identified with mysql_native_password by “1q2w!Q@W”;

grant all on *.* to sysadmin@’%’;

flush privileges;

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

change master to master_host=192.168.245.139, master_port=3306, master_user=repl, master_password=2w3e@W#E, master_auto_position=1;

start slave;

3）检查slave状态

show slave statusG

测试

1）192.168.245.138->192.168.245.139方向

192.168.245.138：

CREATE DATABASE `gohealth-plat` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

USE gohealth-plat;

CREATE TABLE product (product_id int(10) not NULL, product_name varchar(100) not NULL, product_tyep varchar(32) not NULL, sale_price int(10) default 0, input_price int(10) default 0,regist_time date,PRIMARY KEY (product_id));

192.168.245.139：

show databases;

use gohealth-plat;

show tables;

2）192.168.245.139->192.168.245.138方向

192.168.245.139：

use gohealth-plat;

insert into product values(555,’sdfsd’,’sdfd’,54,215,null);

select * from product;

192.168.245.138：

select * from product;

总结

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