mysql8 双主

本文主要是介绍mysql8 双主，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

/data/app/mysql     #应用安装目录

mkdir -p /data/app/src     #应用下载目录
mkdir -p /data/appData/mysql  #数据存储目录
mkdir -p /data/log/mysql     #日志存放目录

groupadd mysql
useradd -g mysql -M -s /sbin/nologin mysql

cd /data/app/src
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz

tar -zxvf mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz -C /data/app
mv /data/app/mysql-8.0.11-linux-glibc2.12-x86_64 /data/app/mysql


masterA 10.2.2.192
masterB 10.2.2.210
同步数据库为test

将my.cnf文件替换为以下内容（两台均执行）
cat > /etc/my.cnf <<EOF

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
user=mysql
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake

default_authentication_plugin=mysql_native_password

port=3306
skip-name-resolve
max_allowed_packet=1024M
default-storage-engine=INNODB
lower_case_table_names=1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
default-time_zone='+8:00'

#skip-grant-tables

max_connections=1000
max_connect_errors=100000

basedir=/data/app/mysql
datadir=/data/appData/mysql
#socket=/data/app/mysql/mysql.sock
pid-file=/data/app/mysql/mysqld.pid
log-error=/data/log/mysql/mysqld.log

long_query_time=1
slow_query_log=1
slow_query_log_file=/data/log/mysql/slow.log


server-id=192 #每一台机器id不同
log-bin=mysql-bin
expire_logs_days=30
slave_skip_errors=all


#binlog-ignore-db=sys,mysql,information_schema,performance_schema
binlog-do-db=test
binlog_format=MIXED
auto-increment-increment=2 #两台机器是2
auto-increment-offset=1 #第一台机器是1第二台是2

EOF

chown -R  mysql:mysql /data/app/mysql /data/appData/mysql /data/log/mysql

/data/app/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize


cp -a /data/app/mysql/support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql

systemctl unmask mysql
systemctl status mysql

systemctl daemon-reload
systemctl enable mysql
systemctl start mysql
systemctl status mysql


查看数据库的密码
cat /data/log/mysql/mysqld.log | grep password

通过/data/app/mysql/bin/mysql -uroot -p 敲回车键进入数据库登陆界面
通过ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yunzhihui123'; 命令来修改密码
exit;
/data/app/mysql/bin/mysql -uroot -pyunzhihui123


root账号授权远程登录
create user 'root'@'%' identified with mysql_native_password by 'yunzhihui123';
grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;

为主库masterA创建slave
创建slave账号并授权
create user 'slave'@'%' identified with mysql_native_password by 'yunzhihui123';
grant replication slave on *.* to 'slave'@'%';
flush privileges;
查看账户是否权限分配正确
show grants for 'slave'@'%';


查看masterA状态，记录二进制文件名和位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |     1809 | test         |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

登陆masterB数据库，执行同步语句
change master to master_host='10.2.2.192',master_user='slave',master_password='yunzhihui123',master_log_file='mysql-bin.000003',master_log_pos=1809;
start slave;
show slave status\G
如果在error没有看到配置错误信息
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
说明配置成功


为masterB创建slave跟主库masterA配置方式基本一样
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |     1809 | test         |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
登陆masterA数据库，执行同步语句
change master to master_host='10.2.2.210',master_user='slave',master_password='yunzhihui123',master_log_file='mysql-bin.000003',master_log_pos=1809;
start slave;
show slave status\G

测试
主库masterA上创建数据库test,并创建表product
create database test;
use test;
create table product( \
       product_id int(10) NOT NULL PRIMARY KEY AUTO_INCREMENT, \
       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
);
登陆masterB数据库
show databases;
use test;
show tables;
desc product;

masterA插入数据
insert into product (product_name,product_tyep) values("waterCup","cup");

masterB插入数据
insert into product (product_name,product_tyep) values("lenovoLaptop","laptop");

select * from product;

这篇关于mysql8 双主的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！