前言
一般小公司安装完免费开源的数据库之后会做数据库主从，这个是很常见的方式，所以我这里写了一下mysql主从。
一、环境配置
1.1 环境
1）主库：mysql安装并配置好
1.系统版本：
[root@vm5 ~]# cat /etc/redhat-release
CentOS release 6.5 (Final)
2.内核：
[root@vm5 ~]# uname -r
2.6.32-431.el6.x86_64
3.IP地址：192.168.0.75（内网，实际上是没有内网的）
4.mysql版本：
[root@localhost ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.17, for Linux (x86_64)using EditLine wrapper
5.mysql安装方式：yum
2）从库：需要重新安装
1.系统版本：预定CentOS Linux
release 7.2.1511 (Core)
2.内核：3.10.0-327.el7.x86_64
3.IP地址：202.x.x.x(外网)、192.168.0.76（内网）
4.mysql版本：5.7.17-1
5. mysql安装方式：yum(方便更新数据库)
2.关闭防火墙iptables/firewalled（主从）
如果不关闭防火墙的话，可以在防火墙中添加允许3306端口访问，这里不作详解
#centos6.5
/etc/init.d/iptables stop
chkconfig iptables off
#centos7禁止firewall开机启动
systemctl stop
firewalld.service
systemctl disable
firewalld.service
3.禁止selinux（主从）
#如果执行“cat /etc/selinux/config”，其中“SELINUX=disabled”则不需要做
#临时关闭防火墙
setenforce off
#永久性关闭防火墙，需要重启服务器
vim /etc/selinux/config
把“SELINUX= enforcing”改为“SELINUX=disabled”
#重启服务器
shutdown -r now
4.时间同步（主从）
#如果“crontab -l”有“/usr/sbin/ntpdate pool.ntp.org”时间同步了，则不需要再配置
yuminstall ntp -y
/usr/sbin/ntpdatepool.ntp.org
echo'#time sync by hua'>>/var/spool/cron/root
echo'*/30 * * * * /usr/sbin/ntpdate pool.ntp.org >/dev/null2>&1'>>/var/spool/cron/root
crontab-l
5.基础安装（主从）
#安装一些常用到的基础命令
yum install -y gcc gcc-c++ vim lrzsz
wget curl man tree rsync
#修改连接数
echo ' #by hua'>> /etc/security/limits.conf
echo '* soft nofile
65535'>> /etc/security/limits.conf
echo '* hard nofile
65535'>> /etc/security/limits.conf
ulimit -n
#重启服务器
shutdown -r now
ulimit -n
二、mysql5.7 yum安装（主库）
2.1.mysql安装（yum）
rpm -ih http://repo.mysql.com/mysql57-community-release-el6.rpm
yum install mysql mysql-server mysql-devel
2.2.修改配置文件
#1）建立相关目录：
mkdir -p
/disk1/logs/mysql/mysql5.7/error
mkdir -p
/disk1/logs/mysql/mysql5.7/slow
mkdir -p /disk1/mysqlData
chown mysql.mysql -R
/disk1/logs/mysql
chown mysql.mysql -R
/disk1/mysqlData
#2）修改my.cnf配置
mv /etc/my.cnf /etc/my.cnf.orig
vi /etc/my.cnf
#编辑配置文件并修改，黑色粗体部分为添加的，删除线部分是修改的,普通黑色是原来的
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
#datadir=/var/lib/mysqldatadir=/disk1/mysqlData
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted securityrisks
symbolic-links=0
#log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid
##by hua尾行添加
#设置密码策略及长度，mysql5.7默认安装并开启validate_password插件
#第一次初始化运行的时候要去掉，否则因mysql初始化生成不了密码而起不来
#validate_password_policy=0
#validate_password_length=4
#或者禁止validate_password
#validate_password=off
port = 3306
#id是唯一的，不能与主库的server_id相同，多个的从库的话也不能与其它从库相同
server_id =1
#设置默认字符集，也可以取消，取消则用安装时的默认字符集（不指定一般为latin1）
#用show variables like
'%char%';命令可以查看安装时的字符集是多少
default-storage-engine=INNODB
character-set-server=utf8
collation-server=utf8_general_ci
innodb_file_per_table=1
#同步一定要开启binlog
log-bin=/disk1/logs/mysql/mysql5.7/mysql-bin
log-queries-not-using-indexes=on
log-error=/disk1/logs/mysql/mysql5.7/error/error.log
#记录慢查询，为了节省性能没开
#slow-query-log = on
#long_query_time = 3
#slow_query_log_file=/disk1/logs/mysql/mysql5.7/slow/slowquery.log
2.3.启动mysql
#mysql启动、停止、重启
service mysqld start
service mysqld stop
service mysqld restart
#检查mysql状态、进程、端口号
service mysqld status
ps -ef |grep mysql
netstat -altnp|grep 3306
#设置开机启动
chkconfig mysqld on
如果启动不起来
方法一：删除原来目录数据，再次启动让其重启生成数据
systemctl stop mysqld.service
ps -ef |grep mysql
rm -rf /disk1/mysqlData
rm -rf /disk1/logs
mkdir -p
/disk1/logs/mysql/mysql5.7/error
mkdir -p
/disk1/logs/mysql/mysql5.7/slow
mkdir -p /disk1/mysqlData
chown mysql.mysql -R
/disk1/logs/mysql
chown mysql.mysql -R
/disk1/mysqlData
service mysqld start
service mysqld status
ps -ef |grep mysql
netstat -altnp|grep 3306
方法二：
如果还是解决不了，有可能selinux没关，请关闭selinux
方法三：
按照上面的方法还是启动不起来，那么有可能是my.cnf没配置正确，或者相关目录没有授权，请仔细检查
2.4. 查看及修改mysql密码
#1）查看mysql初始密码
mysql5.7相对mysql5.6做了新的调整，密码并不是空密码，而是一个至少是8位的随机生成的密码，保存在错误日志中，通过查看/etc/my.cnf得知错误日志的路径我改为了“/disk1/logs/mysql/mysql5.7/error/error.log”，查看日志内容里面的密码
grep "password is" /disk1/logs/mysql/mysql5.7/error/error.log
#登陆测试：
mysql -uroot -p
#2）修改安全级别
第一次登陆mysql之后需要修改密码才能执行操作，否则会报如下错误：
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql5.7默认安装了validate_password安全插件，默认情况是不能少于8位，密码强度为中级，如果我们要修改为简单的（字母+数字、纯数字、纯字母），则会报如下错误：
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
所以我们要要修改安全策略
#设置密码策略及长度
#length为什么要设置 4，因为不管你设置 1、2、3、4，最低长度都是4
set global validate_password_policy=0;
set global validate_password_length=4;
#在修改了mysql密码之后再次登陆才执行下面的命令，就可以看到策略改变了
SHOW VARIABLES LIKE 'validate_password%';
为了永久生效，已经添加到了my.cnf中，把注解去掉
validate_password_policy=0
validate_password_length=4
#重启mysql
service mysqld restart
#3）修改mysql密码
在修改了密码策略及长度的前提下，就可以设置比较简单的密码了
#特别提醒注意的一点是，新版的mysql数据库下的user表中已经没有Password字段了
#下面的语句是把本地为root的账号密码修改为123456
ALTER USER 'root'@'localhost'IDENTIFIED BY '123456';
quit
#再次登陆，密码为123456
mysql -uroot -p
#随便执行一个查询试下
show databases;
quit
如果要添加用户则用：grant PRIVILEGES
#下面是授权hua用户密码为123456，给访问所有数据库的权限，
#客户端IP址址只能是192.168开头的
GRANT ALL PRIVILEGES ON*.* TO 'hua'@'%'IDENTIFIED BY '123456'WITHGRANT OPTION;
flush privileges;
也可以指定权限，下面语句，指定权权限给t1用户，密码为123456，数据库指定为gtlisten
客户端IP址址只能是192.168开头的
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, CREATE TEMPORARY TABLES, DROP, INDEX, ALTER, LOCK TABLES ONgtlisten.* TO't1'@'192.168.%.%' IDENTIFIED BY '123456' WITH GRANT OPTION;
flush privileges;
三、mysql 5.7 yum安装(从库)
mysql从库安装和主库一样，只是配置不同而已，这里就不赘述。
3.1.安装前配置
#安装向centos6兼容的网络命令包
yum install -y net-tools
3.2.mysql安装(yum)
因为版本过了段时间多少都会有一些漏洞，为了方便升级管理，现在采用yum方面安装
#1）安装mysql社会版数据库源并安装mysql，这里会自动找到mysql最新版本
rpm -ih http://repo.mysql.com/mysql57-community-release-el7.rpm
yum install -y mysql mysql-server mysql-devel
#2）建立相关目录
mkdir -p /disk1/logs/mysql/mysql5.7/error
mkdir -p /disk1/logs/mysql/mysql5.7/slow
mkdir -p /disk1/mysqlData
chown mysql.mysql -R /disk1/logs/mysql
chown mysql.mysql -R /disk1/mysqlData
#3）修改my.cnf配置
#先备份
cp /etc/my.cnf /etc/my.cnf.orig
#编辑配置文件并修改，黑色粗体部分为添加的，删除线部分修改过的，普通黑体是原有的
vi /etc/my.cnf
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
#datadir=/var/lib/mysqldatadir=/disk1/mysqlData
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is
recommended to prevent assorted security risks
symbolic-links=0
#log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid
##by hua尾行添加
#设置密码策略及长度，mysql5.7默认安装并开启validate_password插件
#第一次初始化运行的时候要去掉，否则因mysql初始化生成不了密码而起不来
#validate_password_policy=0
#validate_password_length=4
#或者禁止validate_password
#validate_password=off
port = 3306
#id是唯一的，不能与主库的server_id相同，多个的从库的话也不能与其它从库相同
server_id =11
#设置默认字符集，也可以取消，取消则用安装时的默认字符集（不指定一般为latin1）
#用show variables like
'%char%';命令可以查看安装时的字符集是多少
default-storage-engine=INNODB
character-set-server=utf8
collation-server=utf8_general_ci
innodb_file_per_table=1
#从库禁止log-bin
#log-bin=/disk1/logs/mysql/mysql5.7/mysql-bin
###########以下是开启slave的###############################
#不自动启动复制
#skip-slave-start=true
#启用中继日志
relay-log = relay-bin
##relay-log-index 可以不写用默认
relay-log-index = relay-bin.index
#可以设置只读，对个有supper权限的用户不生效，所以root可以写。
read-only = yes
#设置需要忽略同步数据库
#binlog-ignore-db=mysql,test
#设置需要同步的数据库或同步的表，如果多个库，每个库增加一行.
#binlog-do-db=skydb or name.table
################以上是开启slave的###########################
log-queries-not-using-indexes=on
log-error=/disk1/logs/mysql/mysql5.7/error/error.log
#因为是从库禁止记录慢查询，如果读写分离可以开启
#slow-query-log = on
#long_query_time = 3
#slow_query_log_file=/disk1/logs/mysql/mysql5.7/slow/slowquery.log
#4）启动mysql
systemctl start mysqld.service
#mysql停止命令
systemctl stop mysqld.service
#mysql重启命令
systemctl restart
mysqld.service
#设置开机启动
systemctl enable mysqld.service
#检查mysql是否启动
#查看mysql启动状态，绿色部分必须为active (running)
systemctl status mysqld.service
#查看进程及端口，如果进程和端口都存在也说明mysql运行正常
ps -ef |grep mysql
netstat -anltp|grep 3306
#5）启动不起来的处理方法
如果启动不起来
方法一：删除原来目录数据，再次启动让其重启生成数据
systemctl stop mysqld.service
ps -ef |grep mysql
rm -rf /disk1/mysqlData
rm -rf /disk1/logs
mkdir -p /disk1/logs/mysql/mysql5.7/error
mkdir -p /disk1/logs/mysql/mysql5.7/slow
mkdir -p /disk1/mysqlData
chown mysql.mysql -R /disk1/logs/mysql
chown mysql.mysql -R /disk1/mysqlData
systemctl start mysqld.service
systemctl status mysqld.service
方法二：
如果还是解决不了，有可能selinux没关，请看相关环节
方法三：
按照上面的方法还是启动不起来，那么有可能是my.cnf没配置正确，或者相关目录没有授权，请仔细检查
3.3.查看及修改mysql密码
#1）查看mysql初始密码
grep "password is" /disk1/logs/mysql/mysql5.7/error/error.log
#2）修改安全级别
修改/etc/my.cnf，把配置文件中密码策略及长度注解去掉
validate_password_policy=0
validate_password_length=4
#重启mysql
systemctl restart
mysqld.service
#3）修改mysql密码
在修改了密码策略及长度的前提下，就可以设置比较简单的密码了
#特别提醒注意的一点是，新版的mysql数据库下的user表中已经没有Password字段了
#下面的语句是把本地为root的账号密码修改为123456
ALTER USER 'root'@'localhost'IDENTIFIED BY '123456';
quit
#再次登陆，密码为123456
mysql -uroot -p
四、设置mysql主从
4.1.主库（master）上操作
1）修改my.cnf(主库)
因为主mysql主从，主库需如果2个条件：
唯一的server_id
log-bin日志，因为从库是依赖这个日志同步的
以上均已配置
2）建立测试数据库
为了测试随便建立一个hua的数据库，上面建立至少一张表，写一些数据
3）创建具有复制权限的用户（主库）
mysql -uroot -p
#输入密码，登陆，下面是授权repl用户只有同步权限，客户端只能是192.168.开头
GRANT REPLICATION SLAVE ON*.*TO'repl'@'192.168.%.%'IDENTIFIED BY '123456';
FLUSH PRIVILEGES;
quit
4）锁定数据库并备份（主库）
为了防止主库写入，锁定全库的表，语句为FLUSH TABLES WITH READLOCK;
锁定表期间所有数据库只能读，不能写入！！
解锁的语句是unlock tables;
mysql -uroot -p
#输入密码，登陆，锁定全库的表
#注意，锁定表的时候show master status;值是不能变的，除非你解锁，如果变了就说明失败
#锁定表期间，ssh或执行锁定的mysql 客户端是不能关闭或者断开的，否则会锁定失败
#建议用mysql客户端工具执行锁定命令，这样会稳定一些
flush tables with read lock;
show master status;
show master status;
4）备份数据库
打开另一个ssh，连接上主库服务器，然后执行备份命令
mysqldump -uroot -p -B hua >/disk1/backup/mysql/hua2170303.sql
2.从库（slave）上操作
1）修改my.cnf(主库)
因为从库上的my.cnf在安装的时候已经配置好slave了，所以不需要做任何修改
2）还原主库备份到从库
开启mysql主从之后只能保证之后的数据库是一致的，所以为了保持之前的数据库一致性，得还原主库的备份到从库上。
把主库的备份的gtlisten2170302.sql复制到从库，可以使用，sz命令下载到本地然后上传到从库上，如果sz命令用不了则还没安装lrzsz，yum install -y lrzsz安装一下就OK了
#输入下面一条命令，会弹出一个对话框让你选择一个本地目录来保存文件
sz /disk1/backup/mysql/hua20170303.sql
也可以用rsync、scp命令同步过去，具体用法在这里不作解释，下面命令在主库执行
rsync -avz -P /disk1/backup/mysql/* root@192.168.0.76:/disk1/tools/
scp -r /disk1/backup/mysql/* root@192.168.0.76:/disk1/tools/
#还原数据库，我这里同步到了/disk1/tools目录下了
mysql -uroot -p</disk1/tools/hua20170303.sql
mysql -uroot -p -e "show databases;"
3）开启slave同步
从库同步同步需要用到主库的bin日志中的bin名字及pos值（意思就是告诉你从那个地方起开始同步）
从主库执行的show master status;得知bin文件名为“master-bin.000001”,pos值为154
#执行同步命令，注意下面命令是一条命令
CHANGEMASTER TO MASTER_HOST='192.168.0.75',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master-bin.000001',MASTER_LOG_POS=154;
startslave;
#注意G后面是没有;号的
showslave status \G
4.3.主库(master)解锁（重要）
配置好同步，并查看同步正常的情况下，就可以把mysql主库解锁了，回到主库的mysql界面执行，执行解锁命令unlock tables;
附一、常见操作
1. mysql主从复制跳过错误
mysql主从复制，经常会遇到错误而导致slave端复制中断，这个时候一般就需要人工干预，跳过错误才能继续
跳过错误有两种方式：
1.跳过指定数量的事务：
mysql>slave stop;
mysql>SET GLOBALSQL_SLAVE_SKIP_COUNTER = 1 #跳过一个事务
mysql>slave start
2.修改mysql的配置文件，通过slave_skip_errors参数来跳所有错误或指定类型的错误
vi /etc/my.cnf
[mysqld]
#slave-skip-errors=1062,1053,1146
#跳过指定error no类型的错误
#slave-skip-errors=all #跳过所有错误
2.常见其它命令
RESET MASTER #主机端运行,清除所有的日志，这条命令就是原来的FLUSH MASTER
RESET SLAVE #从机运行，清除日志同步位置标志，并重新生成master.info
3.从库重新设置新的同步
#1）停止slave
SLAVE STOP;
#2）重置slave，这个很重要，如果没重置的话，Slave_IO_Running起不来，也可以查看错误日志
RESET SLAVE;
#3）重新执行同步操作
1.主库锁定：show master
status;记录bin文件名及pos值
2.从库再次启动同步：
CHANGE MASTER TO
MASTER_HOST='mater端IP',MASTER_USER='账号',MASTER_PASSWORD='密码',MASTER_LOG_FILE='master-bin名字',MASTER_LOG_POS=pos值;
startslave;
#注意G后面是没有;号的
showslave status \G
3.同步成功后主库解锁
附二、模拟中出现的问题
1. 报错：Incorrect string value: '\xE6\x9D\x8E\xE5\x9B\x9B'
如果没有指定字符集的情况下随便建立一个表，在name字段写入汉字时报如下错误：
ERROR 1366 (HY000): Incorrect string value: '\xE6\x9D\x8E\xE5\x9B\x9B' for column 'name' at row 1
意思是说，name这个字段插入了一个不合法的值。
为什么插入中文就不合法了呢？
因为我们建表的时候，没有为name这个字段指定字符集，所以它默认采用的字符集是“latin1”。
打开Navicat for MySQL工具，在tb1表名上面单击鼠标右键选择“设计表”，选中“name”这一列，在下方就会显示该列对应的字符集：
在建表的时候我们当然可以为每一个字段指定字符集，如果没有指定的话，varchar类型的字段的默认字符集就是“latin1”。前面需要把它手动修改成“utf8”，才能插入中文。
不仅字段有默认的字符集，表也有默认的字符集，如果建表时没有为表指定字符集，表的默认字符集也是“latin1”，我们也要把表的字符集也手动修改为utf8：
永久解决方式：直接在my.cnf设置字符集
#编辑配置文件并修改，黑色粗体部分为添加的
vi /etc/my.cnf
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
#设置默认字符集，也可以取消，取消则用安装时的默认字符集本例为latin1
default-storage-engine=INNODB
character-set-server=utf8
collation-server=utf8_general_ci
做上面的修改之后，再建立表字段就不会是latin1了，这样也不会报错
#重启mysql
systemctl restart
mysqld.service
#登陆mysql查看编码集
mysql -uroot -p -e "show variables like '%char%';"
#修改之前使用的字符集
#修改之后使用的字符集如下：
网友评论