如何配置mysql双主_MySQL双主配置_MySQL

准备环境&＃xff1a;服务器操作系统为RHEL6.4 x86_64&＃xff0c;为最小化安装。主机A和主机B均关闭防火墙和SELINUX ,IP地址分别为192.168.131.129和192.168.131.130&＃xff0c;MySQL版本为5.6.26&＃xff0c;为通用二进制包。

安装MySQL

1.主机A操作

# tar xf mysql-5.6.26-linux-glibc2.5-x86_64.tar.gz

# mv mysql-5.6.26-linux-glibc2.5-x86_64 /usr/local/mysql

# useradd -M -s /sbin/nologin mysql

# chown -R root:root /usr/local/mysql/*

# chown -R mysql:mysql /usr/local/mysql/data/

# yum -y install libaio

# cd /usr/local/mysql/scripts/

# ./mysql_install_db --datadir&＃61;/usr/local/mysql/data/ --user&＃61;mysql --basedir&＃61;/usr/local/mysql/

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

# echo &＃39;export PATH&＃61;$PATH:/usr/local/mysql/bin&＃39; >>/etc/profile

# source /etc/profile

# vim /etc/my.cnf

[mysqld]

datadir&＃61;/usr/local/mysql/data

socket&＃61;/tmp/mysql.sock

user&＃61;mysql

symbolic-links&＃61;0

[mysqld_safe]

log-error&＃61;/var/log/mysqld.log

pid-file&＃61;/var/run/mysqld/mysqld.pid

# service mysqld start

#chkconfig mysqld on

# ss -tunlp | grep 3306

# mysqladmin -uroot password &＃39;123456&＃39; #设置数据库密码

2.主机B操作(同上)

二、配置双主

1、主机A操作

# vim /etc/my.cnf

server-id&＃61;20

log-bin&＃61;mysql-bin

log-bin-index&＃61;mysql-bin.index

replicate-ignore-db&＃61;test

binlog-ignore-db&＃61;mysql

binlog-ignore-db&＃61;information_schema

auto-increment-increment&＃61;2

auto-increment-offset&＃61;1

# service mysqld restart

# mysql -uroot -p123456

mysql> flush tables with read lock;

再打开一个远程终端窗口&＃xff0c;执行以下操作

# mysqldump -uroot -p123456 -B test > test.sql

返回之前的终端窗口&＃xff0c;做以下操作

mysql> unlock tables;

mysql> grant replication slave on *.* to &＃39;wdd&＃39;&＃64;&＃39;192.168.131.130&＃39; identified by &＃39;123456&＃39;;

mysql> flush privileges;

# scp test.sql 192.168.131.130:/root

2、主机B操作

# vim /etc/my.cnf

server-id &＃61; 21

log-bin&＃61;mysql-bin

log-bin-index&＃61; mysql-bin.index

replicate-do-db&＃61;test

binlog-ignore-db&＃61;mysql

binlog-ignore-db&＃61;information_schema

auto-increment-increment&＃61;2

auto-increment-offset&＃61;2

#service mysqld restart

# mysql -uroot -p123456

# mysql -uroot -p123456

mysql> grant replication slave on *.* to &＃39;wdd&＃39;&＃64;&＃39;192.168.131.129&＃39; identified by &＃39;123456&＃39;;

mysql> flush privileges;

3.主机A和主机B分别作如下操作

主机A

①# mysql -uroot -p123456

mysql> SHOW MASTER STATUS;

mysql> change master to

master_host&＃61;&＃39;192.168.131.130&＃39;,

master_user&＃61;&＃39;wdd&＃39;,

master_password&＃61;&＃39;123456&＃39;,

master_log_file&＃61;&＃39; mysql-bin.000002&＃39;,

master_log_pos&＃61;420;

mysql> start slave;

mysql> show slave status\G;

②# mysql -uroot -p123456

mysql> show master status;

mysql> change master to

master_host&＃61;&＃39;192.168.131.129&＃39;,

master_user&＃61;&＃39;wdd&＃39;,

master_password&＃61;&＃39;123456&＃39;,

master_log_file&＃61;&＃39;mysql-bin.000002&＃39;,

master_log_pos&＃61;689;

mysql> start slave;

mysql> show slave status\G;

三、测试

1、主机A进行一下操作

mysql> use test;

mysql> create table info(

-> id int(10) not null auto_increment primary key,

-> name char(20));

mysql> show tables;

2、主机B进行操作

mysql> use test;

mysql> show tables;

mysql> create table person (

-> id int(20) unsigned not null auto_increment primary key,

-> name char(20));

mysql> show tables;

3.在主机A进行操作检验

本条技术文章来源于互联网&＃xff0c;如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源&＃xff1a;php中文网