环境：
节点1：192.168.6.235
节点2：192.168.6.79
【配置第一个节点235】
1. 解压tar包
cd /root/Downloads/
tar -xvf MySQL-5.6.15-1.el6.i686.rpm-bundle.tar
2. 以RPM方式安装MySQL
在RHEL系统中，必须先安装“MySQL-shared-compat-5.6.15-1.el6.i686.rpm”这个兼容包，然后才能安装server和client，否则安装时会出错。
yum install MySQL-shared-compat-5.6.15-1.el6.i686.rpm # RHEL兼容包
yum install MySQL-server-5.6.15-1.el6.i686.rpm # MySQL服务端程序
yum install MySQL-client-5.6.15-1.el6.i686.rpm # MySQL客户端程序
yum install MySQL-devel-5.6.15-1.el6.i686.rpm # MySQL的库和头文件
yum install MySQL-shared-5.6.15-1.el6.i686.rpm # MySQL的共享库
3. 配置MySQL登录密码
cat /root/.mysql_secret # 获取MySQL安装时生成的随机密码
service mysql start # 启动MySQL服务
mysql -uroot -p # 进入MySQL，使用之前获取的随机密码
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('password'); # 在MySQL命令行中设置root账户的
密码为password
quit # 退出MySQL命令行
service mysql restart # 重新启动MySQL服务
4.从新修改登录限制
[sql] view plaincopy
1. [root@localhost ~]# mysql -uroot -p
2. Enter password:
3. Welcome to the MySQL monitor. Commands end with ; or \\g.
4. Your MySQL connection id is 9
5. Server version: 5.6.15 MySQL Community Server (GPL)
6. Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
7. Oracle is a registered trademark of Oracle Corporation and/or its
8. affiliates. Other names may be trademarks of their respective
9. owners.
10. Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.
11. mysql> use mysql;
12. Reading table information for completion of table and column names
13. You can turn off this feature to get a quicker startup with -A
14. Database changed
15. mysql> select host, user from user;
16. +-----------------------+------+
17. | host | user |
18. +-----------------------+------+
19. | 127.0.0.1 | root |
20. | ::1 | root |
21. | localhost | root |
22. | localhost.localdomain | root |
23. +-----------------------+------+
24. mysql> update user set host = '%' where user = 'root';
25. ERROR 1062 (23000): Duplicate entry '%-root' for key 'PRIMARY' -- 这个无所谓
26. mysql> select host, user from user;
27. +-----------------------+------+
28. | host | user |
29. +-----------------------+------+
30. | % | root |
31. | 127.0.0.1 | root |
32. | ::1 | root |
33. | localhost.localdomain | root |
34. +-----------------------+------+
35. 4 rows in set (0.00 sec)
36. mysql> FLUSH PRIVILEGES
37. -> ;
38. Query OK, 0 rows affected (0.00 sec)
39. mysql> select version();
40. +-----------+
41. | version() |
42. +-----------+
43. | 5.6.15 |
44. +-----------+
45. 1 row in set (0.00 sec)
【配置第2个节点79】
以同样的方式配置6.79
步骤同上 （省略）
【主从配置】
先以79作为主数据库，235作从数据库
看看79的/etc/my.cnf
[sql] view plaincopy
1. [root@localhost ~]# more /etc/my.cnf
2. # Example MySQL config file for medium systems.
3. #
4. # This is for a system with little memory (32M - 64M) where MySQL plays
5. # an important part, or systems up to 128M where MySQL is used together with
6. # other programs (such as a web server)
7. #
8. # MySQL programs look for option files in a set of
9. # locations which depend on the deployment platform.
10. # You can copy this option file to one of those
11. # locations. For information about these locations, see:
12. # http://dev.mysql.com/doc/mysql/en/option-files.html
13. #
14. # In this file, you can use all long options that a program supports.
15. # If you want to know which options a program supports, run the program
16. # with the \"--help\" option.
17. # The following options will be passed to all MySQL clients
18. [client]
19. #password = your_password
20. port = 3306
21. socket = /tmp/mysql.sock
22. # Here follows entries for some specific programs
23. # The MySQL server
24. [mysqld]
25. port = 3306
26. socket = /tmp/mysql.sock
27. skip-external-locking
28. key_buffer_size = 16M
29. max_allowed_packet = 1M
30. table_open_cache = 64
31. sort_buffer_size = 512K
32. net_buffer_length = 8K
33. read_buffer_size = 256K
34. read_rnd_buffer_size = 512K
35. myisam_sort_buffer_size = 8M
36. lower_case_table_names=1 #忽略大小写
37. # binary logging format - mixed recommended
38. binlog_format=mixed
39. # required unique id between 1 and 2^32 - 1
40. # defaults to 1 if master-host is not set
41. # but will not function as a master if omitted
42. server-id = 1 #第一个节点 43. 44. 从库 45. 46.
47. log-bin=mysql-bin
48. # Uncomment the following if you are using InnoDB tables
49. #innodb_data_home_dir = /usr/local/mysql/var
50. #innodb_data_file_path = ibdata1:10M:autoextend
51. #innodb_log_group_home_dir = /usr/local/mysql/var
52. # You can set .._buffer_pool_size up to 50 - 80 %
53. # of RAM but beware of setting memory usage too high
54. #innodb_buffer_pool_size = 16M
55. #innodb_additional_mem_pool_size = 2M
56. # Set .._log_file_size to 25 % of buffer pool size
57. #innodb_log_file_size = 5M
58. #innodb_log_buffer_size = 8M
59. #innodb_flush_log_at_trx_commit = 1
60. #innodb_lock_wait_timeout = 50
61. [mysqldump]
62. quick
63. max_allowed_packet = 16M
64. [mysql]
65. no-auto-rehash
66. # Remove the next comment character if you are not familiar with SQL
67. #safe-updates
68. [myisamchk]
69. key_buffer_size = 20M
70. sort_buffer_size = 20M
71. read_buffer = 2M
72. write_buffer = 2M
73. [mysqlhotcopy]
74. interactive-timeout
看看235的/etc/my.cnf
[sql] view plaincopy
1. [root@localhost ~]# more /etc/my.cnf 2. [client] 3. #password = your_password 4. port = 3306 5. socket = /tmp/mysql.sock 6. # Here follows entries for some specific programs 7. # The MySQL server 8. [mysqld] 9. port = 3306 10. socket = /tmp/mysql.sock 11. skip-external-locking 12. key_buffer_size = 16M 13. max_allowed_packet = 1M 14. table_open_cache = 64 15. sort_buffer_size = 512K 16. net_buffer_length = 8K 17. read_buffer_size = 256K 18. read_rnd_buffer_size = 512K 19. myisam_sort_buffer_size = 8M 20. lower_case_table_names=1 # 大小写忽略 21. 22. # binary logging format - mixed recommended 23. binlog_format=mixed 24. # required unique id between 1 and 2^32 - 1 25. # defaults to 1 if master-host is not set 26. # but will not function as a master if omitted 27. server-id = 2 #节点2，每个节点只要不一样就行，不一定是2 28. 29. log-bin = mysql-bin #同步的日志 30. 31. [mysqldump] 32. quick 33. max_allowed_packet = 16M 34. [mysql] 35. no-auto-rehash 36. # Remove the next comment character if you are not familiar with SQL 37. #safe-updates 38. [myisamchk]
在主库79重启mysql
#service mysqld restart #重启mysql
登陆Mysql
mysql> grant replication slave,reload,super on *.* to'sync'@'%' identified by 'mengliang'; #分配一个用户
用于同步
mysql> flush privileges; #权限生效
mysql> show master status;
+------------------+----------+---------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+---------------+------------------+-------------------+
| mysql-bin.000046 | 120 | roamingsms_v1 | mysql,test | |
+------------------+----------+---------------+------------------+-------------------+
1 row in set (0.00 sec)
在从库235
mysql> flush logs;
mysql>stop slave;
mysql>reset slave;
mysql>change master to
master_host='192.168.6.79',master_user='sync',master_password='broadtech',master_log_file='mysql-bin.000046',master_log_pos=120;
mysql> flush privileges;
mysql> show slave status\\G; #查看状态
主从配置成功。
【主从配置】
先以235作为主数据库，79作从数据库
在235
#service mysqld restart #重启mysql
登陆Mysql
mysql> grant replication slave,reload,super on *.* to'sync'@'%' identified by 'mengliang'; #分配一个用户用于同步
mysql> flush privileges; #权限生效
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 | 725 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
然后再79配置
mysql> flush logs;
mysql>stop slave;
mysql>reset slave;
mysql>change master to
master_host='192.168.6.79',master_user='sync',master_password='broadtech',master_log_file='mysql-bin.000007',master_log_pos=725;
mysql> flush privileges;
mysql> show slave status\\G; #查看状态
从主配置完成
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
两项都显示Yes时说明从235同步数据成功。
至此235、79互为主从设置成功！
双机MM完成。
【高可用配置】
1.利用keepalived构建高可用MySQL-HA，保证两台MySQL数据的一致性，然后用keepalived实现虚拟VIP，通过keepalived自带的服务监控功能来实现MySQL故障时自动切换实现failover （下面详细实施步骤）
2.mmm模式 （以后慢慢介绍...）
3.mha模式（以后慢慢介绍...）
4.cluster模式（以后慢慢介绍...）
先在79上配置
安装keepalived
# wget http://www.keepalived.org/software/keepalived-1.2.2.tar.gz
# yum -y install gcc gcc-c++ openssl-devel
# tar xf keepalived-1.2.2.tar.gz
# cd keepalived-1.2.2
# ./configure --prefix=/usr/local/keepalived
# make && make install
# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
# mkdir /etc/keepalived
keepalived实现虚拟IP，通过keepalived自带的服务监控功能来实现MySQL故障时自动切换；
1、keepalived设置：
6.79服务器上面，编辑keeplaived.conf配置文件：
vi /usr/local/keepalived/etc/keepalived/keepalived.conf
[sql] view plaincopy
1. [root@localhost keepalived]# vi /usr/local/keepalived/etc/keepalived/keepalived.conf
2. ! Configuration File for keepalived
3. global_defs {
4. router_id mysql-ha
5. }
6. vrrp_instance VI_1 {
7. ! Configuration File for keepalived
8. global_defs {
9. router_id mysql-ha
10. }
11. vrrp_instance VI_1 {
12. state BACKUP
13. interface eth0
14. virtual_router_id 79
15. priority 100
16. advert_int 1
17. nopreempt
18. authentication {
19. auth_type PASS
20. auth_pass 123456
21. }
22. virtual_ipaddress {
23. 192.168.6.82
24. }
25. }
26. virtual_server 192.168.6.82 3306 {
27. delay_loop 2
28. lb_algo rr
29. lb_kind DR
30. persistence_timeout 60
31. protocol TCP
32. real_server 192.168.6.79 3306 {
33. weight 1
34. notify_down /usr/local/keepalived/etc/keepalived/mysql.sh
35. TCP_CHECK {
36. connect_port 3306
37. connect_timeout 3
38. nb_get_retry 2
39. delay_before_retry 1
40. }
41. }
42. }
1.编辑mysql服务停止后的切换脚本：mysql.sh
[root@localhost /]# vi /usr/local/keepalived/etc/keepalived/mysql.sh
2、启动79上面的keepalived
[root@localhost /]# /usr/local/keepalived/sbin/keepalived -f
/usr/local/keepalived/etc/keepalived/keepalived.conf -D
3. ping 192.168.6.82发现已经可以ping通，并且用192.168.6.82这个IP也能够连接到数据库服务器，这里实现了235和79 虚拟成82.
面对任意一台的死机，failover，可以透明。
先在235上配置
重复79的操作。
Keepalived双主高可用配置已经配置好了，可以测试。
因篇幅问题不能全部显示，请点此查看更多更全内容