MYSQL5.6 AA复制+高可用HA的相关配置

QQ空间新浪微博腾讯微博更多

2013 年 2 月 17 日5450

转帖请注明出处： alexclouds.net

首先还是我在上一篇文章说的，如果要做到AA复制，必须要启用MYSQL的相关功能，只靠YUM安装是不行的，必须源代码安装。

mysql 的数据同步，在 mysql 官网上叫 replication。意思就是复制。

mysql 为了实现 replication 必须打开 bin-log 项，也是打开二进制的 mysql 日志记录选项。

mysql 的 bin-log 二进制日志，可以记录所有影响到数据库表中存储记录内容的 sql 操作，如 insert | update | delete 操作，而不记录 select 这样的操作。因此，我们可以通过二进制日志把某一时间段内丢失的数据可以恢复到数据库中，如果二进制日志中记录的日志项，包涵数据库表中所有数据，那么就可以恢复全部数据了。

这个二进制日志，用作远程数据库恢复，那就是 replication 了。这就是使用 replication 而不用 sync 的原因。

在同步过程中，最重要的同步参照物，就是同步使用哪一个二进制日志文件，从哪一条记录开始同步。

 

0、目录的选择

包安装位置(prefix) /usr/local/

数据库文件位置： /data/mysql/

数据库日志位置： /data/log

数据库备份目录： /data/backup

1、卸载默认的mysql及apache的rpm包

#yum remove httpd mysql mysql-server php php-cli php-common php-devel php-gd -y

2、更新时间，如果有多台服务器请都要设置.

service ntpd stop

ntpdate cn.pool.ntp.org

service ntpd start

chkconfig ntpd on

3、安装之前先安装编译环境及需要的库文件

#yum install gcc gcc-c++ make ncurses-devel -y

4、安装CMAKE

wget http://www.cmake.org/files/v2.8/cmake-2.8.6.tar.gz

解压并安装make install cmake.

cmake 2.8.4也可以，随便哪个。

wget http://www.cmake.org/files/v2.8/cmake-2.8.4.tar.gz

5 、安装mysql

下载源码包，我是在oracle上注册后得到此下载地址的：

下载MYSQL

#wget http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.10.tar.gz

然后解压mysql-5.6.10.tar.gz，进入解压后的目录运行指令：

#cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DENABLED_LOCAL_INFILE=1 -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=all

出现如下画面，没有错误

接着

# make

#make install

6、配置MYSQL

userdel -r mysql
groupadd -r -g 3306 mysql
useradd -u 3306 -g mysql -r -M -s /sbin/nologin mysql
mkdir -p /data/mysql/
mkdir -p /data/log
chown -R mysql:mysql /data/
cp support-files/my-medium.cnf /etc/my.cnf
cp support-files/my-huge.cnf /etc/my.cnf

/usr/local/mysql/scripts/mysql_install_db  -user=mysql  -basedir=/usr/local/mysql  -datadir=/data/mysql &  //初始化数据库

 

[root@mysql1 mysql-5.6.10]# /usr/local/mysql/scripts/mysql_install_db  -user=mysql  -basedir=/usr/local/mysql  -datadir=/data/mysql &
[1] 30796
[root@mysql1 mysql-5.6.10]# 
[root@mysql1 mysql-5.6.10]# 
[root@mysql1 mysql-5.6.10]# 
[root@mysql1 mysql-5.6.10]# 
[root@mysql1 mysql-5.6.10]# Installing MySQL system tables...
[root@mysql1 mysql-5.6.10]# 
[root@mysql1 mysql-5.6.10]# 2013-02-17 00:43:04 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-02-17 00:43:04 30801 [Note] InnoDB: The InnoDB memory heap is disabled
2013-02-17 00:43:04 30801 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2013-02-17 00:43:04 30801 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-02-17 00:43:04 30801 [Note] InnoDB: CPU does not support crc32 instructions
2013-02-17 00:43:04 30801 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-02-17 00:43:04 30801 [Note] InnoDB: Completed initialization of buffer pool
2013-02-17 00:43:04 30801 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2013-02-17 00:43:04 30801 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2013-02-17 00:43:04 30801 [Note] InnoDB: Database physically writes the file full: wait...
2013-02-17 00:43:04 30801 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2013-02-17 00:43:04 30801 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2013-02-17 00:43:04 30801 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2013-02-17 00:43:04 30801 [Warning] InnoDB: New log files created, LSN=45781
2013-02-17 00:43:04 30801 [Note] InnoDB: Doublewrite buffer not found: creating new
2013-02-17 00:43:04 30801 [Note] InnoDB: Doublewrite buffer created
2013-02-17 00:43:04 30801 [Note] InnoDB: 128 rollback segment(s) are active.
2013-02-17 00:43:04 30801 [Warning] InnoDB: Creating foreign key constraint system tables.
2013-02-17 00:43:04 30801 [Note] InnoDB: Foreign key constraint system tables created
2013-02-17 00:43:04 30801 [Note] InnoDB: Creating tablespace and datafile system tables.
2013-02-17 00:43:04 30801 [Note] InnoDB: Tablespace and datafile system tables created.
2013-02-17 00:43:04 30801 [Note] InnoDB: Waiting for purge to start
2013-02-17 00:43:04 30801 [Note] InnoDB: 1.2.10 started; log sequence number 0
2013-02-17 00:43:05 30801 [Note] Binlog end
2013-02-17 00:43:05 30801 [Note] InnoDB: FTS optimize thread exiting.
2013-02-17 00:43:05 30801 [Note] InnoDB: Starting shutdown...
2013-02-17 00:43:06 30801 [Note] InnoDB: Shutdown completed; log sequence number 1625977
OK

Filling help tables...2013-02-17 00:43:06 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-02-17 00:43:06 30824 [Note] InnoDB: The InnoDB memory heap is disabled
2013-02-17 00:43:06 30824 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2013-02-17 00:43:06 30824 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-02-17 00:43:06 30824 [Note] InnoDB: CPU does not support crc32 instructions
2013-02-17 00:43:06 30824 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-02-17 00:43:06 30824 [Note] InnoDB: Completed initialization of buffer pool
2013-02-17 00:43:06 30824 [Note] InnoDB: Highest supported file format is Barracuda.
2013-02-17 00:43:06 30824 [Note] InnoDB: 128 rollback segment(s) are active.
2013-02-17 00:43:06 30824 [Note] InnoDB: Waiting for purge to start
2013-02-17 00:43:06 30824 [Note] InnoDB: 1.2.10 started; log sequence number 1625977
2013-02-17 00:43:06 30824 [Note] Binlog end
2013-02-17 00:43:06 30824 [Note] InnoDB: FTS optimize thread exiting.
2013-02-17 00:43:06 30824 [Note] InnoDB: Starting shutdown...
2013-02-17 00:43:08 30824 [Note] InnoDB: Shutdown completed; log sequence number 1625987
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

  /usr/local/mysql/bin/mysqladmin -u root password 'new-password'
  /usr/local/mysql/bin/mysqladmin -u root -h mysql1.alexclouds.net password 'new-password'

Alternatively you can run:

  /usr/local/mysql/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

  cd . ; /usr/local/mysql/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

  cd mysql-test ; perl mysql-test-run.pl

Please report any problems with the ./bin/mysqlbug script!

The latest information about MySQL is available on the web at

  http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

New default config file was created as /usr/local/mysql/my.cnf and
will be used by default by the server when you start it.
You may edit this file to change server settings

WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
--defaults-file argument to mysqld_safe when starting the server

[1]+  Done                    /usr/local/mysql/scripts/mysql_install_db -user=mysql -basedir=/usr/local/mysql -datadir=/data/mysql

 

 

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

chmod +x /etc/init.d/mysqld

/chkconfig --add mysqld

chkconfig mysqld on

service mysqld start

 

[root@mysql1 mysql-5.6.10]# cp support-files/mysql.server /etc/init.d/mysqld
[root@mysql1 mysql-5.6.10]# chmod +x /etc/init.d/mysqld
[root@mysql1 mysql-5.6.10]# chkconfig --add mysqld
[root@mysql1 mysql-5.6.10]# chkconfig mysqld on
[root@mysql1 mysql-5.6.10]# service mysqld start
Starting MySQL.                                            [  OK  ]

设置mysql密码

/usr/local/mysql/bin/mysqladmin -u root password 'new-password'
/usr/local/mysql/bin/mysqladmin -u root -h mysql1.alexclouds.net password 'new-password'

也可以netstat 看看3306起来了没

 建立命令连接

# cd /usr/local/bin //进入用户的默认搜索路径下建立mysql命令的软连接，可以直接执行mysql命令

ln -s /usr/local/mysql/bin/mysql mysql
ln -s /usr/local/mysql/bin/mysqldump mysqldump
ln -s /usr/local/mysql/bin/mysqladmin mysqladmin
ln -s /usr/local/mysql/bin/mysqlbinlog

7、配置MYSQL 的 MASTER到MASTER的主主同步

1、环境描述。
MYSQL 版本为5.6.10

主机：172.28.17.152（A）  主机名mysql1.alexclouds.net
主机：172.28.17.153（B）   主机名mysql2.alexclouds.net

2、 创建授权用户。
A：

mysql> grant replication slave on *.* to 'user'@'172.28.17.153' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

B：

mysql> grant replication slave on *.* to 'user'@'172.28.17.152' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
然后都停止MYSQL 服务器。
3、配置文件。
在两个机器上的my.cnf里面都开启二进制日志。

A：
user = mysql
log-bin=mysql-bin
server-id= 1
binlog-do-db=test
binlog-ignore-db=mysql
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1

B：
user = mysql
log-bin=mysql-bin
server-id= 2
binlog-do-db=test
binlog-ignore-db=mysql
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=2
至于这些参数的说明具体看手册。
红色的部分非常重要，如果一个MASTER 挂掉的话，另外一个马上接管。
紫红色的部分指的是服务器频繁的刷新日志。这个保证了在其中一台挂掉的话，日志刷新到另外一台。从而保证了数据的同步。
4、重新启动MYSQL服务器。
在A和B上执行相同的步骤
[root@ha1 ~]#service mysqld restart
5、进入MYSQL的SHELL。
A：
mysql> flush tables with read lock\G
Query OK, 0 rows affected (0.00 sec)
mysql> show master status\G

B：
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status\G

然后备份自己的数据，保持两个机器的数据一致。
mysqldump -u root -pXXXX --all-databases > /root/all.sql

方法还有很多完了后看下一步。
6、在各自机器上执行CHANGE MASTER TO命令。
A：
change master to
master_host='172.28.17.153',
master_user='user',
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=120;
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
B：

change master to
master_host='172.28.17.152',
master_user='user',
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=120;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
7、查看各自机器上的IO进程和 SLAVE进程是否都开启。
A：
mysql> show processlist\G

B上：
mysql> show processlist\G

如果 没有“master has sent akk binlog to slave; waiting for binlog to be update”出现，检查DATA目录下的错误文件。必须出了问题。

博主我就出了点小问题，提示两个MYSQL的UUID相同，冲突了。 博主我不禁纳闷，但是转念一想，我本次写文章截图的主机用的测试是VM的镜像的，两个MYSQL的UUID必须相同啊。于是赶紧改为不一样的：

去哪里改呢？看下图：

 

8、释放掉各自的锁，然后进行插数据测试。
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
插入之前两个机器表的对比：
A：
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
2 rows in set (0.00 sec)
B：
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
2 rows in set (0.00 sec)
从A机器上进行插入
A：
mysql> create table test
-> (id int not null auto_increment primary key,
-> str varchar(255) not null) engine myisam;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test(str) values
-> ('This is a master to master test table');
Query OK, 1 row affected (0.01 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
2 rows in set (0.00 sec)
mysql> select * from test;
+----+---------------------------------------+
| id | str |
+----+---------------------------------------+
| 1 | This is a master to master test table |
+----+---------------------------------------+
1 row in set (0.00 sec)
现在来看B机器：
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
2 rows in set (0.00 sec)
mysql> select * from t11_replicas;
+----+---------------------------------------+
| id | str |
+----+---------------------------------------+
| 1 | This is a master to master test table |
+----+---------------------------------------+
1 row in set (0.00 sec)
现在反过来从B机器上插入数据：
B：
mysql> insert test(str) values('This is a test 2');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t11_replicas;
+----+---------------------------------------+
| id | str |
+----+---------------------------------------+
| 1 | This is a master to master test table |
| 2 | This is a test 2 |
+----+---------------------------------------+
2 rows in set (0.00 sec)
我们来看A
A：
mysql> select * from test;
+----+---------------------------------------+
| id | str |
+----+---------------------------------------+
| 1 | This is a master to master test table |
| 2 | This is a test 2 |
+----+---------------------------------------+
2 rows in set (0.00 sec)
现在两个表互相为MASTER。
多MASTER自增字段冲突的问题。
具体文章见：
http://dev.mysql.com/tech-resources/articles/advanced-mysql-replication.html

 

高可用

使用MySQL双master+keepalived是一种非常好的解决方案，在MySQL-HA环境中，MySQL互为主从关系，这样就保证了两台MySQL数据的一致性，然后用keepalived实现虚拟IP，通过keepalived自带的服务监控功能来实现MySQL故障时自动切换。

主机ha1和ha2上安装heartbeat
yum –y install heartbeat

配置heartbeat
略去，下一节再写