​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 status\G

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 status\G

[测试]

1)192.168.245.138->192.168.245.139方向
192.168.245.138:
CREATEDATABASE gohealth-plat CHARACTER SET ‘utf8mb4′ COLLATE’utf8mb4_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进行查看是什么原因导致的,好了,今天的分享就到此结束了哦!!

来源:IT那活儿,本文观点不代表自营销立场,网址:https://www.zyxiao.com/p/124287

发表评论

登录后才能评论
侵权联系
返回顶部