Mysql 5.6 双主互备高可用（Keepalived+mysql） （一）

安装环境：Redhat 5.8

mysql版本：mysql-5.6.25

DB机器A（主）：192.168.8.193 主机名：mysql01

DB机器B：192.168.8.194 主机名：mysql02

mysql VIP（IP） ：192.168.8.198

---------------------------------------------------------------------------------------------------------------------

分别设置两台服务器的hosts解析：

找到/etc/hosts文件并编辑添加以下内容：

192.168.8.193      mysql01

192.168.8.194      mysql02

----------------------------------------------------------------------------------------------------------------------

mysql 5.5 版本以后，开始使用cmake编译工具，所以在安装源文件中已经没有configure文件。

我们首先安装相应的依赖包，包括一些编译需要用到的工具：

yum -y install cmake gcc gcc-c++ ncurses-devel bison libaio-devel libnl* libpopt* popt-static openssl-devel

------------------------------------------------------------------------------------------------------------------------

增加mysql用户

groupadd mysql 

useradd mysql -g mysql -M -s /sbin/nologin

mkdir /usr/local/mysql

mkdir /usr/local/mysql/data 

-g：指定新用户所属的用户组

-M：不建立用户目录 

-s：/sbin/nologin代表用户不能登录系统

--------------------------------------------------------------------------------------------------------------------------

进入opt目录解压安装包：

tar -zxvf mysql-5.6.25.tar.gz

编译安装：

cd mysql-5.6.25 进入解压后的目录执行：

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DSYSCONFDIR=/etc \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 -DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 -DWITH_READLINE=1 \
-DMYSQL_UNIX_ADDR=/tmp/mysqld.sock \
-DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DEXTRA_CHARSETS=all \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_SSL:STRING=bundled

说明：

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql        //安装目录

-DINSTALL_DATADIR=/usr/local/mysql/data         //数据库存放目录

---------------------------------------------------------------------------------------------------------------------------

然后再执行安装命令：

make && make install

# make是用来编译的，它从Makefile中读取指令，然后编译。
# make install是用来安装的，它也从Makefile中读取指令，将软件安装到指定的位置。

---------------------------------------------------------------------------------------------------------------------------

执行完没有报错后我们再将mysql的配置文件拷贝到 /etc/mysql 目录下：

mkdir    /etc/mysql    #  新建mysql目录

cp  /opt/mysql-5.6.25/support-files/my-default.cnf     /etc/mysql/my.cnf

Mysql在启动实例的时候会读取配置文件，如果它找不到配置文件则会按照默认参数设置启动实例，

这里我们可以通过help命令查看到它的几个默认加载路径：

/usr/local/mysql/bin/mysql --help | grep my.cnf

order of preference, my.cnf, $MYSQL_TCP_PORT,

/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf

Mysql 会以读取到最后一个配置文件中的参数为准

-----------------------------------------------------------------------------------------------------------------------------

完成后，我们进入到 /usr/local/mysql 目录下，进行一些安全设置：

chown -R mysql:mysql data                  #将data目录的权限赋给mysql用户

chmod -R 700 /usr/local/mysql/data    #将data目录的权限设置为只有属主有读、写、执行权限

-----------------------------------------------------------------------------------------------------------------------------

然后将mysql作为系统服务，加入开机启动项：

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

chmod 755 mysqld     # 需要执行权限

chkconfig --add mysqld

输入 chkconfig --list mysqld 查看结果是否如下:

mysql.server    0:关闭  1:关闭  2:启用  3:启用  4:启用  5:启用  6:关闭

chkconfig  mysqld on  #  设置开机启动

-----------------------------------------------------------------------------------------------------------------------------

然后配置 mysql 的环境变量，执行如下命令：

echo  export  MYSQL_HOME=/usr/local/mysql  >>  /etc/profile

echo  'PATH=$PATH:$HOME/bin:$MYSQL_HOME/bin'   >>  /etc/profile

source /etc/profile

---------------------------------------------------------------------------------------------------------------------------

修改DB机器A  mysql 配置文件：

vi  /etc/mysql/my.cnf

[mysqld]

port = 3306

socket = /tmp/mysql.sock

basedir = /usr/local/mysql

datadir = /data/mysql

pid-file = /data/mysql/mysql.pid

user = mysql

bind-address = 0.0.0.0

skip-locking

skip-name-resolve

back_log = 384

key_buffer_size = 256M

max_allowed_packet = 4M

thread_stack = 256K

table_cache = 128K

sort_buffer_size = 6M

read_buffer_size = 4M

join_buffer_size = 8M

myisam_sort_buffer_size = 64M

table_cache = 512

thread_cache_size = 64

query_cache_size = 64M

tmp_table_size = 256M

max_connections = 768

max_connect_errors = 10000000

wait_timeout = 10

thread_concurrency = 8

skip-networking

# 以上为基本设置，请根据自己的使用场景进行优化，但请确保有以下内容

server-id=1                             #节点标识号

log-bin=/usr/local/mysql/log/bin-log            #定义二进制bin日志路径

relay-log=/usr/local/mysql/log/relay-log      #定义中继日志relaylog

log-slave-updates = ON                                 #中继日志执行之后，这些变化是否需要记入自己的binarylog

replicate-wild-ignore-table = mysql.%         #复制过滤选项

replicate-wild-ignore-table = test.%

replicate-wild-ignore-table = information_schema.%

replicate-wild-ignore-table = performance_schema.%

#DB A 配置配置至此结束

-----------------------------------------------------------------------------------------

修改DB机器B  mysql 配置文件：

vi  /etc/mysql/my.cnf

[mysqld]

port = 3306

socket = /tmp/mysql.sock

basedir = /usr/local/mysql

datadir = /data/mysql

pid-file = /data/mysql/mysql.pid

user = mysql

bind-address = 0.0.0.0

skip-locking

skip-name-resolve

back_log = 384

key_buffer_size = 256M

max_allowed_packet = 4M

thread_stack = 256K

table_cache = 128K

sort_buffer_size = 6M

read_buffer_size = 4M

join_buffer_size = 8M

myisam_sort_buffer_size = 64M

table_cache = 512

thread_cache_size = 64

query_cache_size = 64M

tmp_table_size = 256M

max_connections = 768

max_connect_errors = 10000000

wait_timeout = 10

thread_concurrency = 8

skip-networking

[mysqld]

server-id=2  #节点标识号

log-bin=/usr/local/mysql/log/bin-log

relay-log=/usr/local/mysql/log/relay-log

log-slave-updates = ON

replicate-wild-ignore-table = mysql.%

replicate-wild-ignore-table = test.%

replicate-wild-ignore-table = information_schema.%

replicate-wild-ignore-table = performance_schema.%

auto-increment-increment=10

auto-increment-offset=1

DB B 配置配置至此结束

----------------------------------------------------------------------------------------

注意：

在主库上请勿使用以下选项

#binlog-ignore-db = mysql

#binlog-ignore-db = test

#binlog-ignore-db = information_schema

#binlog-ignore-db = performance_schema

在从库上请勿使用以下选项

#binlog-do-db

#binlog-ignore-db

----------------------------------------------------------------------------------------------------------------------------

mysql 安装完成后，我们需要初始化 mysql 数据库：

/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

启动 mysql 服务命令：

service   mysqld  start

-----------------------------------------------------------------------------------------------------------------------------

安全设置，我们需要运行 mysql_secure_installation  执行如下几个设置：

为root用户设置密码，删除匿名账号，取消root用户远程登录，删除test库和对test库的访问权限，刷新授权表使修改生效

/usr/local/mysql/bin/mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):     回车键
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n] y     是否设置 root 用户密码
New password:                       设置并输入 root 密码
Re-enter new password:        再一次输入
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y        删除匿名用户
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n       是否允许 root 用户远程登陆
 ... skipping.

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y       是否删除 test 测试数据库
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y         是否重新加载权限表
 ... Success!




All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!


Cleaning up...

-------------------------------------------------------------------------------------------------------------------------------------

mysql_install_db 的附加说明：

当我们需要初始化mysql数据库时，或者需要在本机新加一个新的实例时，这个命令对我们很有帮助：

我们只需要将 mysql 的配置文件 my.cnf 复制一份到新的 mysql 目录下（无需安装，假设为/usrl/local/mysql_2/），

并将端口和目录都相应的改变即可。配置完成后，运行下面命令进行数据库的初始化：：

/usr/local/mysql/bin/mysql_install_db --defaults-file=/usrl/local/mysql_2/my.cnf --datadir=/usrl/local/mysql_2/data

-------------------------------------------------------------------------------------------------------------------------------------

对mysql用户设置远程访问权限

改表法：登陆mysql后，更改“mysql”数据库里的“user”表里的“host”项，将“localhost”改“%” 

mysql -u root -p

mysql>use mysql;

mysql>update user set host ='%' where user ='root';

mysql>select host,user from user; 

+-----------+---------+
| host           | user |
+-----------+----------+
| %                 | root |
| 127.0.0.1   | root |
| ::1                | root |
| mysql01     | root |
+-----------+----------+

mysql>FLUSH PRIVILEGES

授权法：假设允许用户username通过密码password从远程连接到mysql服务器

mysql>GRANT ALL RRIVILEGES ON *.* TO username@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

mysql>FLUSH PRIVILEGES;

-------------------------------------------------------------------------------------------------------------------------------------

DB A上需要创建用于备份的用户：

grant replication slave on *.* to 'repl_user'@'192.168.8.194' identified by 'repl_passwd';

show master status;

输出的内容，下面需要使用到：MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=7145;

DB B上也需要创建用于备份的用户：

grant replication slave on *.* to 'repl_user'@'192.168.8.193' identified by 'repl_passwd';

show master status;

输出的内容，下面需要使用到：MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=600;

---------------------------------------------------------

在DB A 上开启同步，执行：

CHANGE MASTER TO

       MASTER_HOST='192.168.8.194',

       MASTER_USER='repl_user',

       MASTER_PASSWORD='repl_passwd',

       MASTER_LOG_FILE='bin-log.000003',

       MASTER_LOG_POS=600;

启动slave服务:

mysql> start slave;

查看slave运行状态：

mysql> show slave status\G;

两个都是Yes， 说明开启成功：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

在DB B 上开启同步，执行：

CHANGE MASTER TO

       MASTER_HOST='192.168.8.193',

       MASTER_USER='repl_user',

       MASTER_PASSWORD='repl_passwd',

       MASTER_LOG_FILE='bin-log.000004',

       MASTER_LOG_POS=7145;

启动slave服务:

mysql> start slave;

查看slave运行状态：

mysql> show slave status\G;

两个都是Yes， 说明开启成功：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

--------------------------------------------------------------------------------------------------------------------------------------

至此，两台mysql主主互备的安装配置部分结束！