Linux下 MySQL 互为主从配置

Linux系统环境：CentOS 6.5
master1：192.168.100.204
master2：192.168.100.205
mysql版本：mysql-5.6.19
注：mysql的5.5以下的版本和5.5以上的版本配置方法不一样以下两个节点安装
1：防火墙打开相应端口
# /sbin/iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
# /etc/rc.d/init.d/iptables save2：安装相关依赖包
# yum -y install gcc gcc-c++ ncurses ncurses-devel openssl openssl-devel cmake perl lsof bison3：删除自带的mysql
# rpm -qa|grep mysql
# rpm -e --allmatches --nodeps mysql-libs-5.1.71-1.el6.x86_644：创建相关目录
# mkdir /linuxidc/soft
# mkdir -p /linuxidc/mysql/data/5：创建mysql用户
# /usr/sbin/groupadd mysql
# /usr/sbin/useradd -s /sbin/nologin -M -g mysql mysql
# chown -R mysql:mysql /linuxidc/mysql/6：安装mysql
# cd /linuxidc/soft
# tar -zxvf mysql-5.6.19.tar.gz
# cd mysql-5.6.19
# cmake -DCMAKE_INSTALL_PREFIX=/linuxidc/server/mysql -DMYSQL_DATADIR=/linuxidc/mysql/data -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 -DSYSCONFDIR=/etc/ -DWITH_SSL=yes -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_READLINE=on
# make && make install7：修改相关目录权限并创建软连接
# chmod +w /linuxidc/server/mysql
# chown -R mysql:mysql /linuxidc/server/mysql/
# ln -s /linuxidc/server/mysql/lib/lib* /usr/lib/
# ln -s /linuxidc/server/mysql/bin/mysql /usr/bin8：修改配置文件
# cp /linuxidc/server/mysql/support-files/my-default.cnf /etc/my.cnf
# vi /etc/my.cnf[mysqld]
character-set-server = utf8
default-storage-engine = MyISAM
basedir = /linuxidc/server/mysql
datadir = /linuxidc/mysql/data
log-error = /linuxidc/mysql/mysql_error.log
pid-file = /linuxidc/mysql/mysql.pid
max_allowed_packet = 32M
explicit_defaults_for_timestamp = true9：安装mysql数据库
# /linuxidc/server/mysql/scripts/mysql_install_db --basedir=/linuxidc/server/mysql --datadir=/linuxidc/mysql/data --user=mysql10：设置mysql开机自动启动服务
# cp /linuxidc/server/mysql/support-files/mysql.server /etc/init.d/mysqld
# chkconfig --add mysqld
# chkconfig --level 345 mysqld on11：修改mysqld文件并启动mysql
# vi /etc/init.d/mysqld#修改mysqld文件中的下面两项
basedir=/linuxidc/server/mysql
datadir=/linuxidc/mysql/data# service mysqld start12：创建需同步的数据库
# mysql -uroot -p
mysql> create database linuxidc;
mysql> GRANT ALL PRIVILEGES ON linuxidc.* TO doiido@"%" IDENTIFIED BY "linuxidc123";
mysql> exit--------------------以下在服务器master1上操作
修改配置文件
# vi /etc/my.cnf[mysqld]
#默认是1，master和slave不能相同即可
server-id=1
log-bin=bin.log
port=3306
binlog-do-db =linuxidc
#要复制的数据库
replicate-do-db =linuxidc重启mysql
# service mysqld restart建立用于同步的账户
# mysql -uroot -p
mysql> grant replication slave on *.* to "dodo"@"192.168.100.205" identified by "jjjjjj";查看File对应值，并记录下来
mysql> show master status;+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| bin.000001 | 120 | linuxidc | | |
+------------------+----------+--------------+------------------+-------------------+mysql> exit--------------------以下在服务器master2上操作
2.修改slave上的配置文件my.cnf
# vi /etc/my.cnf[mysqld]
server-id=2
port=3306
log-bin=bin.log
binlog-do-db =linuxidc
replicate-do-db =linuxidc重启mysql
# service mysqld restart在从服务器上建立相应的数据库
# mysql -uroot -p将192.168.100.204设置为自己的主服务器
mysql> CHANGE MASTER TO
MASTER_HOST="192.168.100.204",MASTER_PORT=3306,
MASTER_USER="dodo",
MASTER_PASSWORD="jjjjjj",
MASTER_LOG_FILE="bin.000001",
MASTER_LOG_POS=120;启动从服务器复制功能
mysql> start slave;mysql> show slave statusG;...................
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...................注：#Slave_IO_Running：连接到主库，并读取主库的日志到本地，生成本地日志文件
#Slave_SQL_Running:读取本地日志文件，并执行日志里的SQL命令。
上面两个必须都是yes，其中一个NO均属错误mysql> exit至此，单向主从配置完毕，如需要互为主从则配置如下--------------------以下在服务器master2上操作
建立用于同步的账户
# mysql -uroot -p
mysql> grant replication slave on *.* to "dodo"@"192.168.100.204" identified by "jjjjjj";查看File对应值，并记录下来
mysql> show master status;+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| bin.000001 | 649 | linuxidc | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set （0.00 sec）mysql> exit--------------------以下在服务器master1上操作
将192.168.100.205设置为自己的主服务器
mysql> CHANGE MASTER TO
MASTER_HOST="192.168.100.205",
MASTER_PORT=3306,
MASTER_USER="dodo",
MASTER_PASSWORD="jjjjjj",
MASTER_LOG_FILE="bin.000001",
MASTER_LOG_POS=649;启动从服务器复制功能mysql> start slave;mysql> show slave statusG;...................
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...................上面两个必须都是yes，其中一个NO均属错误mysql> exit至此互为主从配置完毕--------------------------------------分割线 --------------------------------------Ubuntu 14.04下安装MySQL http://www.linuxidc.com/Linux/2014-05/102366.htm《MySQL权威指南（原书第2版）》清晰中文扫描版 PDF http://www.linuxidc.com/Linux/2014-03/98821.htmUbuntu 14.04 LTS 安装 LNMP NginxPHP5 （PHP-FPM）MySQL http://www.linuxidc.com/Linux/2014-05/102351.htmUbuntu 14.04下搭建MySQL主从服务器 http://www.linuxidc.com/Linux/2014-05/101599.htmUbuntu 12.04 LTS 构建高可用分布式 MySQL 集群 http://www.linuxidc.com/Linux/2013-11/93019.htmUbuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb http://www.linuxidc.com/Linux/2013-08/89270.htmMySQL-5.5.38通用二进制安装 http://www.linuxidc.com/Linux/2014-07/104509.htm--------------------------------------分割线 --------------------------------------本文永久更新链接地址