Linux下 Mysql 互为主从

系统环境: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 save


2:安装相关依赖包
#  yum -y install gcc gcc-c++ ncurses ncurses-devel openssl openssl-devel cmake perl lsof bison

3:删除自带的mysql
#  rpm -qa|grep mysql
#  rpm -e --allmatches --nodeps mysql-libs-5.1.71-1.el6.x86_64


4:创建相关目录
#  mkdir /doiido/soft
#  mkdir -p /doiido/mysql/data/


5:创建mysql用户
#  /usr/sbin/groupadd mysql
#  /usr/sbin/useradd -s /sbin/nologin -M -g mysql mysql
#  chown -R mysql:mysql /doiido/mysql/ 


6:安装mysql
#  cd /doiido/soft
#  tar -zxvf mysql-5.6.19.tar.gz
#  cd mysql-5.6.19
#  cmake -DCMAKE_INSTALL_PREFIX=/doiido/server/mysql -DMYSQL_DATADIR=/doiido/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 install

7:修改相关目录权限并创建软连接
#  chmod +w /doiido/server/mysql
#  chown -R mysql:mysql /doiido/server/mysql/ 
#  ln -s /doiido/server/mysql/lib/lib* /usr/lib/
#  ln -s /doiido/server/mysql/bin/mysql /usr/bin

8:修改配置文件
#  cp /doiido/server/mysql/support-files/my-default.cnf /etc/my.cnf
#  vi /etc/my.cnf

[mysqld]
character-set-server = utf8
default-storage-engine = MyISAM
basedir = /doiido/server/mysql
datadir = /doiido/mysql/data
log-error = /doiido/mysql/mysql_error.log
pid-file = /doiido/mysql/mysql.pid
max_allowed_packet = 32M
explicit_defaults_for_timestamp = true 

9:安装mysql数据库
#  /doiido/server/mysql/scripts/mysql_install_db --basedir=/doiido/server/mysql --datadir=/doiido/mysql/data --user=mysql

10:设置mysql开机自动启动服务
#  cp /doiido/server/mysql/support-files/mysql.server /etc/init.d/mysqld
#  chkconfig --add mysqld
#  chkconfig --level 345 mysqld on 


11:修改mysqld文件并启动mysql
#  vi /etc/init.d/mysqld
#修改mysqld文件中的下面两项
basedir=/doiido/server/mysql
datadir=/doiido/mysql/data
#  service mysqld start

12:创建需同步的数据库
#  mysql -uroot -p
mysql>  create database doiido;
mysql>  GRANT ALL PRIVILEGES ON doiido.* TO doiido@"%" IDENTIFIED BY ‘doiido123‘;
mysql>  exit



--------------------以下在服务器master1上操作
修改配置文件
#  vi /etc/my.cnf
[mysqld]
#默认是1,master和slave不能相同即可
server-id=1
log-bin=bin.log
port=3306
binlog-do-db =doiido
#要复制的数据库
replicate-do-db =doiido

重启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 | doiido       |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
mysql>  exit



--------------------以下在服务器master2上操作
2.修改slave上的配置文件my.cnf
#  vi /etc/my.cnf
[mysqld]
server-id=2
port=3306
log-bin=bin.log
binlog-do-db =doiido
replicate-do-db =doiido

重启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 status\G;
...................
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 | doiido       |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
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 status\G;
...................
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...................
上面两个必须都是yes,其中一个NO均属错误

mysql>  exit


至此互为主从配置完毕

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。