Mysql双主配置及安装部署
IT那活儿
不管IT什么活儿，干就完了。
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）
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
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进行查看是什么原因导致的，好了，今天的分享就到此结束了哦！！