vlambda博客
学习文章列表

​Mysql双主配置及安装部署

IT那活儿
不管IT什么活儿,干就完了。
284篇原创内容
Official Account


亲爱滴伙伴们,今天给大家分享一下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进行查看是什么原因导致的,好了,今天的分享就到此结束了哦!!


END