[高可用web服务器集群]Mysql主从复制

MariaDB简述
MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可。开发这个分支的原因之一是：甲骨文公司收购了MySQL后，有将MySQL闭源的潜在风险，因此社区采用分支的方式来避开这个风险。

mariadb主服务器配置：

hostname MysqlMaster
yum update
yum -y install mariadb-server mariadb-client #安装mysql
systemctl start mariadb
mysql_secure_installation #初始化mysql

————————以下为脚本文件————————
#!/bin/bash

##定义变量
export mysqlbinpath="/usr/bin" #mysql路径
export master_mysql_root_passwd="123"  #mysql主数据库的root密码
export replication_user="copyuser"  #用于复制的mysql用户
export replication_passwd="123456" #mysql用户copydb的密码
export master_ip="172.16.1.10"    #mysql主服务器IP
export replication_db="moodle_data" #需要同步的数据库名

##配置my.cnf
cat > /etc/my.cnf << EOF
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin #开启二进制日志
server_id=1 #表示mysql服务器
binlog-do-db=$replication_db #镜像数据库名称
bind-address=$master_ip #绑定IP地址

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#

!includedir /etc/my.cnf.d
EOF

#重启数据库
systemctl restart mariadb

##建立同步数据库用户供从服务器使用
{
${mysqlbinpath}/mysql -uroot -p${master_mysql_root_passwd} <<EOF
CREATE USER '$replication_user'@'localhost' IDENTIFIED BY '$replication_passwd';
GRANT REPLICATION SLAVE ON *.* TO $replication_user IDENTIFIED BY '$replication_passwd' WITH GRANT OPTION;
FLUSH PRIVILEGES;
WITH READ LOCK FLUSH TABLES;
EOF
}

##导出数据库内容
${mysqlbinpath}/mysqldump -uroot -p${master_mysql_root_passwd} ${replication_db} > ${replication_db}.sql

————————脚本结束————————
scp moodle_data.sql [email protected]:/root/ #传输sql文件到从服务器

#等待主从同步后登录mysql解锁表
UNLOCK TABLES;
#更新数据库
mysql_upgrade -uroot -p123

#建立mysql用户供web服务器登录
insert into mysql.user(Host,User,Password) values("%","webServerUser",password("123456"));
FLUSH PRIVILEGES;
GRANT all PRIVILEGES on moodle_data.* to 'webServerUser'@'%' IDENTIFIED by '123456';

mariadb主服务器配置：

hostname MysqlSlave
yum update
yum -y install mariadb-server mariadb-client #安装mysql
systemctl start mariadb
mysql_secure_installation #初始化mysql

————————以下为脚本文件————————
#!/bin/bash

##定义变量
export slave_mysql_root_passwd="123"  #mysql主数据库的root密码
export mysqlbinpath="/usr/bin" #mysql路径
export replication_user="copyuser"  #用于复制的mysql用户
export replication_passwd="123456" #mysql用户copydb的密码
export master_ip="172.16.1.10"    #mysql主服务器IP
export slave_ip="172.16.1.11"    #mysql从服务器IP
export replication_db="moodle_data" #需要同步的数据库名
export master_logfile="mariadb-bin.000007" #主服务器日志名称
export master_pos="245" #当前pos值

##配置my.cnf
cat > /etc/my.cnf << EOF
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin #开启二进制日志
server_id=2 #表示mysql服务器
replicate-do-db=$replication_db #镜像数据库名称
bind-address=$slave_ip #绑定IP地址

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#

!includedir /etc/my.cnf.d
EOF

#重启数据库
systemctl restart mariadb

#建立数据库授予权限
{
${mysqlbinpath}/mysql -uroot -p${slave_mysql_root_passwd} <<EOF
drop database if exists $replication_db;
create database $replication_db;
GRANT ALL PRIVILEGES ON $replication_db.* TO '$replication_user'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EOF
}

#导入sql文件
mysql -u root -p123 ${replication_db} < ${replication_db}.sql

#更改主从关系
{
${mysqlbinpath}/mysql -uroot -p${slave_mysql_root_passwd} <<EOF
CHANGE MASTER TO 
MASTER_HOST='$master_ip',
MASTER_USER='$replication_user',
MASTER_PASSWORD='$replication_passwd',
MASTER_PORT=3306,
MASTER_LOG_FILE='$master_logfile',
MASTER_LOG_POS=$master_pos,
MASTER_CONNECT_RETRY=10;

START SLAVE;
EOF
}

————————脚本文件结束————————

#更新数据库
mysql_upgrade -u root -p123

#建立mysql用户供web服务器登录
insert into mysql.user(Host,User,Password) values("%","webServerUser",password("123456"));
FLUSH PRIVILEGES;
GRANT all PRIVILEGES on moodle_data.* to 'webServerUser'@'%' IDENTIFIED by '123456';