Mysql |mysql部署与主从同步（源码）

三、Mysql服务器部署

3.1 mysql服务器基础功能部署

3.1.1连接、创建文件夹、上传文件包

（1）通过xshell和xftp工具连接mysql服务器。

（2）创建文件夹

cd /opt
mkdir software
mkdir mysql

（3）上传文件

3.1.2查看gcc环境

rpm -qa | grep glibc

3.1.3创建并查看用户组和用户

groupadd mysql
useradd -r -g mysql -s /bin/false mysql

cat /etc/group
cat /etc/passwd

3.1.4解压文件、创建软连接

（1）解压文件

cd /opt/mysql
tar -zxvf /opt/software/mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz

（2）创建软连接

cd /usr/local
ln -s /opt/mysql/mysql-5.7.28-linux-glibc2.12-x86_64 mysql
ls

3.1.5配置文件

（1）创建mysql-files文件

cd /usr/local/mysql
mkdir mysql-files

（2）将mysql文件夹授权给相应的用户和组

chown -R mysql.mysql /usr/local/mysql
ll

3.1.6初始化数据库

bin/mysqld --initialize --user=mysql --basedir=/opt/mysql/mysql-5.7.24-linux-glibc2.12-x86_64/ --datadir=/opt/mysql/mysql-5.7.24-linux-glibc2.12-x86_64/data

或（图中用的下面的）

bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data

若出现以下问题： 解决方法：
yum install libaio-devel.x86_64

结果：初始化数据库，设置了data文件夹，并获取临时密码

3.1.7加入服务

（1）基础路径配置

mv /etc/my.cnf /etc/my.cnf.old
vim /etc/my.cnf

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rcKP8VYs-1569737411681)(%E4%B8%89%E3%80%81Mysql%E6%9C%8D%E5%8A%A1%E5%99%A8%E9%83%A8%E7%BD%B2.assets/1566630244113.png)]

配置文件内容如下：

[mysqld]

basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
lower_case_table_names=1

（2）配置服务脚本

cd /usr/local/mysql
cp support-files/mysql.server /etc/init.d/mysql
chmod a+x /etc/init.d/mysql
chkconfig --add /etc/init.d/mysql

chkconfig mysql on
systemctl start mysql
ps -ef | grep mysql

（3）添加为环境变量：

vim /etc/profile

文件末尾添加以下内容 ：

export PATH=$PATH:/usr/local/mysql/bin

source /etc/profile

3.1.8登录数据库

（1）登录数据库，并修改密码，设置权限啊

mysql -uroot -p

如果提示下图错误

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ru1N2JKY-1569737411689)(%E4%B8%89%E3%80%81Mysql%E6%9C%8D%E5%8A%A1%E5%99%A8%E9%83%A8%E7%BD%B2.assets/1566630287027.png)]

则说明环境变量没有添加好

解决方法为输入：ln -s /usr/local/mysql/bin/mysql /usr/bin

或者从新配置环境变量。

**详见：**https://www.cnblogs.com/effortsing/p/10009984.html1

（2）修改数据库密码

alter user root@'localhost' identified by 'string@1307';

（3）使用用户

use mysql;

（4）允许远程登录

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

（5）刷新生效

flush privileges;

3.1.9打开防火墙

（1）打开防火墙的3306端口

firewall-cmd --zone=public --add-port=3306/tcp –permanent

（2）刷新防火墙规则

   firewall-cmd --reload

（3）查看防火墙端口

firewall-cmd --zone=public --list-ports

3.1.10通过Navicat登录验证

3.2 mysql服务器主从同步配置

3.2.1主服务器配置

（1）修改主服务器的my.cnf配置文件（该文件一般路径为 vim /etc/my.cnf）

log-bin=master-a-bin #日志文件名称，不能随意改动
binlog-format=ROW #二进制日志的格式，有row、statement、mixed三种类型
server-id=1 #要求各服务器的id必须不同，建议以ip作为该标记
log-slave-updates=ture #从服务器也要开启备份，作为另一个的主服务器（可开可不开）

​ 完整代码如下：

[mysqld]
basedir=/opt/mysql/mysql-5.7.24-linux-glibc2.12-x86_64
datadir=/opt/mysql/mysql-5.7.24-linux-glibc2.12-x86_64/data


log-bin=master-a-bin
binlog-format=ROW
server-id=1
binlog-do-db=stringdb

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Fne10O7T-1569737411696)(%E4%B8%89%E3%80%81Mysql%E6%9C%8D%E5%8A%A1%E5%99%A8%E9%83%A8%E7%BD%B2.assets/1566562698207-1566630728866.png)]

（2）登录主数据库，在主服务器数据库中授权从服务器登录

mysql> alter user root@'localhost'indentified by 'hgjsxy@string';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QiXBHRbz-1569737411697)(%E4%B8%89%E3%80%81Mysql%E6%9C%8D%E5%8A%A1%E5%99%A8%E9%83%A8%E7%BD%B2.assets/1566563079665.png)]

3.2.2从服务器配置

（1）修改从服务器的my.cnf配置文件（该文件一般路径为 vim /etc/my.cnf）和主服务器的不同之处也就是id。

完整代码如下：

[mysqld]
basedir=/opt/mysql/mysql-5.7.24-linux-glibc2.12-x86_64
datadir=/opt/mysql/mysql-5.7.24-linux-glibc2.12-x86_64/data


log-bin=master-a-bin
binlog-format=ROW
server-id=2

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iH3VojuM-1569737411699)(%E4%B8%89%E3%80%81Mysql%E6%9C%8D%E5%8A%A1%E5%99%A8%E9%83%A8%E7%BD%B2.assets/1566563304461.png)]

（2）重启主从服务器，加载配置

systemctl start mysql

3.2.3指针配置

（1）分别登录主、从数据库，并初始化主从数据库，保证初始数据相同。

简单的说，就是在主从数据库中都建立一个名字相同的，如“string”的数据库，并保证里面的表都相同）

#登录数据库
mysql -uroot -p
#查看数据库
show databases;
#创建数据库
create database string;
#核对数据库
#以上最简单的方法就是直接在navicat可视化界面中直接操作

（2）查看主数据库状态

mysql> show master status;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UMuOUh4I-1569737411701)(%E4%B8%89%E3%80%81Mysql%E6%9C%8D%E5%8A%A1%E5%99%A8%E9%83%A8%E7%BD%B2.assets/1566563707029.png)]

（3）配置从数据库

mysql> stop slave;     #停止从服务，首次配置不需要此命令，因为就没有开启从服务

#根据上文主数据库中"show master status;"的结果，进行下面配置

mysql> CHANGE MASTER TO
    -> MASTER_HOST='10.0.2.1',
    -> MASTER_USER='root',
    -> MASTER_PASSWORD='hgjsxy@string',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='master-a-bin.000001',
    -> MASTER_LOG_POS=154,
    -> MASTER_CONNECT_RETRY=10;
    
    
mysql> CHANGE MASTER TO MASTER_HOST='10.0.2.3',MASTER_USER='root',MASTER_PASSWORD='hgjsxy@string',MASTER_PORT=33006,MASTER_LOG_FILE='master-a-bin.000002',MASTER_LOG_POS=262334679,MASTER_CONNECT_RETRY=10;

当开始同步时，查看从库状态，配置无错误并显示两个Yes时，基本无问题。

start slave;
show slave status\G；

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0zanYFiP-1569737411703)(%E4%B8%89%E3%80%81Mysql%E6%9C%8D%E5%8A%A1%E5%99%A8%E9%83%A8%E7%BD%B2.assets/1566564315571.png)]

（4）测试主从同步

方法一：（推荐）
直接客户端连接
主创建、从查看

方法二：
#主创建表
mysql>show databases;
mysql>use test;
mysql>CREATE TABLE `text2` (
    'id' int(11) not null,
    'name' varchar(255) default null,
    primary key ('id')
)engine=innodb default charset=latin;

#主从查看：
mysql>use test
mysql>show table

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nZp5N9Iz-1569737411704)(%E4%B8%89%E3%80%81Mysql%E6%9C%8D%E5%8A%A1%E5%99%A8%E9%83%A8%E7%BD%B2.assets/1566564601780.png)]

3.3 Linux中Mysql大小写敏感问题和同步出错问题解决方案

（1）Mysql大小写敏感问题

lower_case_table_names & lower_case_file_system

〇 lower_case_file_system
该参数是用于描述data目录所在的操作系统是否为大小写敏感，该参数为bool类型，但无法修改。
0 – 大小写敏感
1 – 大小写不敏感

​ 比如跑在linux上的都是OFF或者0。

〇 lower_case_table_names
该参数为静态，可设置为0、1、2。

0 – 大小写敏感。（Unix，Linux默认）
创建的库表将原样保存在磁盘上。如create database TeSt;将会创建一个TeSt的目录，create table AbCCC …将会原样生成AbCCC.frm。
SQL语句也会原样解析。

1 – 大小写不敏感。（Windows默认）
创建的库表时，MySQL将所有的库表名转换成小写存储在磁盘上。
SQL语句同样会将库表名转换成小写。
如需要查询以前创建的Test_table（生成Test_table.frm文件），即便执行select * from Test_table，也会被转换成select * from test_table，致使报错表不存在。

2 – 大小写不敏感（OS X默认）
创建的库表将原样保存在磁盘上。
但SQL语句将库表名转换成小写。

修改lower_case_table_names导致的常见不良隐患：
如果在lower_case_table_names=0时，创建了含有大写字母的库表，改为lower_case_table_names=1后，则会无法被查到。

注意事项：
将默认的lower_case_tables_name为0设置成1，需先将已经存在的库表名转换为小写：

**1）**针对仅表名存在大写字母的情况：
①、lower_case_tables_name=0时，执行rename table成小写。
②、设置lower_case_tables_name=1，重启生效。

2）针对库名存在大写字母的情况：
①、lower_case_tables_name=0时，使用mysqldump导出，并删除老的数据库。
②、设置lower_case_tables_name=1，重启生效。
③、导入数据至实例，此时包含大写字母的库名已转换为小写

查看方法：（登录数据库后输入以下命令）

mysql> show variables like '%case%';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qB0WuRRt-1569737411706)(%E4%B8%89%E3%80%81Mysql%E6%9C%8D%E5%8A%A1%E5%99%A8%E9%83%A8%E7%BD%B2.assets/1566565218295.png)]

修改方法：

用 root 登录，修改 vim /etc/my.cnf （注意：以实际 my.cnf 配置文件路径为准）
在 [mysqld] 节点下，加入一行： lower_case_table_names=1，重启 MySQL 即可；

lower_case_table_names=1

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YFaFHEgr-1569737411708)(%E4%B8%89%E3%80%81Mysql%E6%9C%8D%E5%8A%A1%E5%99%A8%E9%83%A8%E7%BD%B2.assets/1566565477763-1566630899252.png)]

3.重启Mysql生效并检测

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZkEBJDm6-1569737411710)(%E4%B8%89%E3%80%81Mysql%E6%9C%8D%E5%8A%A1%E5%99%A8%E9%83%A8%E7%BD%B2.assets/1566565598350.png)]

（2）Mysql复制中断

参照上文重新配置

（3）MySQL从库列类型不一致导致复制异常问题

slave_type_conversions 这个参数在mysql5.5.3 引入，目的是启用row 格式的bin-log 的时候，如果主从的column 的数据类型不一致，会导致复制失败，mysql5.5.3 之后支持，主库是int 从库是bigint 这种类型的复制，

这个参数的意义就是控制些类型转换容错性。

如果从库的字段类型范围比主库类型大，那么设置slave_type_conversions=ALL_NON_LOSSY后复制没有问题的。

如果从库类型比主库类型小，比如从int 复制到tinyint ，虽然可以通过设置slave_type_conversions=ALL_LOSSY,ALL_NON_LOSSY让主从不出问题，但是实际上会造成数据丢失的风险。

几种值的设置：

ALL_LOSSY：允许数据截断

ALL_NON_LOSSY：不允许数据截断，如果从库类型大于主库类型，是可以复制的，反过了，就不行了，从库报复制错误，复制终止。

ALL_LOSSY,ALL_NON_LOSSY: 所有允许的转换都会执行，而不管是不是数据丢失。

空值（不设置）：要求主从库的数据类型必须严格一致，否则都报错。

Mode	Effect
ALL_LOSSY	In this mode, type conversions that would mean loss of information are permitted.This does not imply that non-lossy conversions are permitted, merely that only cases requiring either lossy conversions or no conversion at all are permitted; for example, enabling only this mode permits an INT column to be converted to TINYINT (a lossy conversion), but not a TINYINT column to an INT column (non-lossy). Attempting the latter conversion in this case would cause replication to stop with an error on the slave.
ALL_NON_LOSSY	This mode permits conversions that do not require truncation or other special handling of the source value; that is, it permits conversions where the target type has a wider range than the source type. 【确保从库的列类型更宽泛些也不会导致复制报错】Setting this mode has no bearing on whether lossy conversions are permitted; this is controlled with the ALL_LOSSY mode. If only ALL_NON_LOSSY is set, but not ALL_LOSSY, then attempting a conversion that would result in the loss of data (such as INT to TINYINT, or CHAR(25) to VARCHAR(20)) causes the slave to stop with an error.
ALL_LOSSY,ALL_NON_LOSSY	When this mode is set, all supported type conversions are permitted, whether or not they are lossy conversions.
ALL_SIGNED	Treat promoted integer types as signed values (the default behavior).
ALL_UNSIGNED	Treat promoted integer types as unsigned values.
ALL_SIGNED,ALL_UNSIGNED	Treat promoted integer types as signed if possible, otherwise as unsigned.
[empty]	When slave_type_conversions is not set, no attribute promotion or demotion is permitted; this means that all columns in the source and target tables must be of the same types.This mode is the default.

生产环境一个案例：

从库是有个Oracle系统去查数据的，原先从库的cp_shop_activity表示utf8mb4字符集，oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8，修改后的字段类型如下图：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9b44ovgO-1569737411712)(%E4%B8%89%E3%80%81Mysql%E6%9C%8D%E5%8A%A1%E5%99%A8%E9%83%A8%E7%BD%B2.assets/170406204743491.png)]

但是没几天后，发现主从复制报错了，错误码1677。从库上记录的错误日志如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oR3LnB4d-1569737411713)(%E4%B8%89%E3%80%81Mysql%E6%9C%8D%E5%8A%A1%E5%99%A8%E9%83%A8%E7%BD%B2.assets/170406204743492.png)]

我们看下主库的mode字段是varchar(50)，从库的mode字段是varchar(200)。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CtJrqBse-1569737411715)(%E4%B8%89%E3%80%81Mysql%E6%9C%8D%E5%8A%A1%E5%99%A8%E9%83%A8%E7%BD%B2.assets/170406204743493.png)]

在从库设置：

stop slave;
set global slave_type_conversions=ALL_NON_LOSSY; 

# 默认slave_type_conversions为空，表示强制从库和主库的字段类型一致，不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;
show slave status\G  

这样即可解决这个问题。

官方文档：https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-04/142578.htm

stop slave;
set global slave_type_conversions=ALL_NON_LOSSY; 
start slave;
show slave status\G       #此处不要加“；”号

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ALKNMdG7-1569737411717)(%E4%B8%89%E3%80%81Mysql%E6%9C%8D%E5%8A%A1%E5%99%A8%E9%83%A8%E7%BD%B2.assets/1566567425340.png)]

，不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;
show slave status\G  

这样即可解决这个问题。

官方文档：https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-04/142578.htm

stop slave;
set global slave_type_conversions=ALL_NON_LOSSY; 
start slave;
show slave status\G       #此处不要加“；”号

[外链图片转存中…(img-ALKNMdG7-1569737411717)]