一、卸载旧版mysql
查看rpm包
rpm -qa|grep mysql
卸载mysql
rpm -e --nodeps mysql-libs-5.1.73-8.el6_8.x86_64
查找mysql残留包,
有则删除,没有则忽略
find / -name mysql
一个一个的rm –rf 删除,再查
载后/etc/my.cnf不会删除,需要进行手工删除
rm -rf /etc/my.cnf
二、下载配置安装MySql5.7 linux版
我在这里下载的是linux通用版
网址:https://dev.mysql.com/downloads/mysql/
1.先上传到交换空间里再从交换空间复制到跳板机(堡垒机)的本地磁盘中
2.通过finshell传输到服务器的/usr/local/文件下
3.解压mysql压缩包
[root@localhost ~]# cd /usr/local/
[root@localhost local]# ls
bin etc games include lib lib64 libexec mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz sbin share src
[root@localhost local]# tar -zxvf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
#重命名为mysql
[root@localhost local]# mv mysql-5.7.37-linux-glibc2.12-x86_64/ mysql
4.创建用户组及用户
#先检查mysql组和用户是否存在,如无创建
[root@localhost ~]# cat /etc/group | grep mysql
[root@localhost ~]# cat /etc/passwd | grep mysql
#删除用户和组
userdel mysql
groupdel mysql
创建用户组
groupadd mysql
创建用户
useradd -r -g mysql mysql
5.修改工作目录权限
cd /usr/local/mysql
chown -R mysql:mysql ./
6.在etc目录下创建my.cnf文件
自5.7.18之后就不在提供my-default.cnf文件,这里需要自行添加所需参数,如下:
[mysqld]
skip-name-resolve
datadir=/data/mysql_data/mysql
socket=/data/mysql_data/mysql/mysql.sock
user=mysql
character_set_server=utf8
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
# 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]
pid-file=/data/mysql_data/mysql_log/mariadb.pid
log=/data/mysql_data/mysql_log/log.log
#日志
slow-query-log_file=/data/mysql_data/mysql_log/slow-query-log.log
#慢查询日志
log-error=/data/mysql_data/mysql_log/log-err.log
#错误日志
log-queries-not-using-indexes = /data/mysql_data/mysql_log/nouseindex.log
#不使用索引的日志查询
[client]
port=3306
socket=/data/mysql_data/mysql/mysql.sock
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
创建my.cnf文件
my.cnf里边去掉NO_ZERO_DATE
7.执行安装,进入bin目录下
./mysqld --initialize --user=mysql
注意保存密码
8.修改目录权限,进入mysql目录
chown -R root:root ./
chown -R mysql:mysql data (有权限,后续才能在在data下创建所需文件)
三、启动MySQL服务
拷贝mysql服务,进入mysql目录
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
如果mysql.server不是可执行文件,则需要使用chmod命令赋值
注册开机启动服务
chkconfig --add mysql
chkconfig --list
启动mysql服务
service mysql start
此时启动时可能会报错(本次启动时报错)
解决方案,做个软连接
ln -s /var/lib/mysql/mysql.sock /tmp
当然有时可能是启动命名写错了,注意区分
再进行配置环境变量。
配置成功后可在任意地方启动mysql 输入mysql -uroot -p即可启动
如果未配置环境变量需要在mysql的bin目录下,输入./mysql -uroot -p
密码在安装的时候就会显示
拓展: 当mysql的存储位置发生改变时,虽然服务可以启动成功,但是无法进入mysql
原因:当mysql的存储位置发生改变时,软连接失效,需要删除如软连接
删除软连接时,注意要用rm -rf ./mysql.sock(删除软链接,但不删除实际数据)
错误的删除方式rm -rf ./test_chk_ln/ (这样就会把原来test_chk下的内容删除)
1.配置环境变量
[root@localhost mysql]# vi /etc/profile
在最后添加
MYSQL_HOME=/usr/local/mysql
PATH=$PATH:$MYSQL_HOME/bin
export PATH MYSQL_HOME
# 刷新生效
[root@localhost mysql]# source /etc/profile
2.进入Mysql
输入mysql -uroot -p
输入密码,输入密码时是不会显示有输入,一直都是空白。所以要确定自己输入的密码时正确的,可以先写在文本上,再右键复制上去
3.更新密码
set PASSWORD = PASSWORD('ZZYpeixian@2022');
4.开启远程连接权限
update user set host='%' where user='root';
刷新
flush privileges;
5.防火墙开启3306端口远程访问
1. 查看防火墙
firewall-cmd --state #查看防火墙状态
或者:systemctl status firewalld #查看防火墙状态
2.查看3306端口状态
firewall-cmd --zone=public --query-port=3306/tcp
3.如果是no,表示关闭,打开3306端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent
4、防火墙重载
firewall-cmd --reload
5、再次查看3306状态
firewall-cmd --zone=public --query-port=3306/tcp
6、应该是yes,说明端口已经打开,去navicat测试连接,输入ip和新的Mysql账户密码,即可成功!
6.本地Navicat连接服务器Mysql
首先上传Navicat的安装包和破解脚本到交换空间
再从交换空间移动到本地磁盘
首先安装Navicat,安装完成后,点击试用,然后关闭软件
再启动破解脚本,点击path(此时一定保证软件是关闭状态)
选择Navicat的安装路径
然后打开软件
将生成的keygen复制进去
完成后删除跳板机(堡垒机)上的破解脚本(所有操作均可以在无网络的情况下完成)
四、冷迁移(mysqldump的备份恢复)
1.将之前的sql文件备份同步到Linux中的问题
备份时注意不要备份四个系统库,具体解决方案往后看
(而且堡垒机在传输文件一小时无操作时会断开,尝试修改跳转机休眠时间,无效,文件太大会影响传输。结合现场实际环境将每个库单独备份)
(尝试使用定时模拟鼠标电击器但是还是没有效果)
将导出的databak.sql文件上传到交换空间,然后上传到服务器
注意过程中使用XFTP速度最快,可以下载一个
备份过程中,以下两种都是传输错误
2.数据库恢复思路
将之前的sql文件备份同步到Linux中。
思路:1、根据数据量库表单独备份,2、压缩后上传至跳板机再解压,3、再上传至服务器,4、再恢复到 数仓服务器的mysql中。
注意:备份时注意不要备份四个系统库,具体解决方案往后看。
影响因素:(而且堡垒机在传输文件一小时无操作时会断开,尝试修改跳转机休眠时间,无效,文件太大会影响传输。结合现场实际环境将每个库单独备份)(尝试使用定时模拟鼠标电击器但是还是没有效果)
五、mysqldump备份
1.备份工具
本次使用的是mysqldump,当然还有其他的备份方式比如myisam备份、innodb备份、一些备份工具。mysqldump 是mysql官方提供的。(简单,备份时间长,如果是简单的备份 / 恢复,使用它就足够了)xtrabackup、mydumper。
2.mysqldump的备份方法,本次由于数据量大,采用交叉混合的备份方式
2.1备份选项:
--all-databases:备份所有数据库。等同于-A | --databases db1 db2:备份指定的数据库。等同于 -B |
--single-transaction:对事务引擎执行热备 | --flush-logs:更新二进制日志文件 |
--master-data=2。 1:每备份一个库就生成一个新的二进制文件(默认) 2:只生成一个新的二进制文件 | -d 只导出结构不导出数据 |
--tables:备份指定的表 | -t 只导出数据不导出结构 |
--events: 备份事件调度器。简写为-E | --routines: 备份存储过程和存储函数。简写为-R |
–add-drop-table防止数据表重名 | --quick:在备份大表时指定该选项 |
–extended-insert (-e):此参数表示可以多行插入 | add-locks在INSERT数据之前和之后锁定和解锁具体的数据表 |
–opt:此Mysqldump命令参数是可选的,如果带上这个选项代表激活了Mysqldump命令的quick,add-drop-table,add-locks,extended-insert,lock-tables参数,也就是通过–opt参数在使用Mysqldump导出Mysql数据库信息时不需要再附加上述这些参数。 |
2.2备份方式
2.1单独备份(不需要考虑编码问题)
1)备份指定数据库。(全量备份mysql某个数据库例如:testdb数据库) mysqldump -uroot -pmysql --databases testdb > databases.sql 2)备份指定数据库中的指定表。(例如test中的demo表) Mysqldump -uroot -pmysql --databases testdb.demo > demo.sql 3)mysql 数据导出排除指定某个表 mysqldump --database test --ignore-table=test.a > test.sql 4)mysql 数据导出排除指定的多个表--多表间并列 mysqldump --database test --ignore-table=test.a --ignore-table=test.b --ignore-table=test.c > test.sql 5)备份多个数据库。例如:db1,db2 Mysqldump -uroot -pmysql --databases db1 db2 > databases.sql 6)只导出表结构 不导出数据 mysqldump -uroot -pmysql --opt -d demo > xxx.sql 7)导出数据不导出结构 Mysqldump -uroot -pmysql -t demo > xxx.sql 8)mysqldump导出表的部分数据 mysqldump -uroot -pmysql -h192.168.0.106 -P3306 库名 表名 --where=" id <10" > test.sql 9)导出一个库中多个表, 表名中间用空格隔开就行,如导出dba库下的表t1和 t2 mysqldump -uroot -pmysql -h192.168.0.106 -P3306 dba t1 t2 > /download/t1_t2.sql 举例子: mysqldump -uroot -pmysql -h192.168.0.106 -P3306 his50 -R -E > F:\QY\his50.sql mysqldump -uroot -pmysql -h192.168.0.106 -P3306 mhis -R -E > F:\QY\mhis.sql mysqldump -uroot -pmysql -h192.168.0.106 -P3306 -B emr50 hiss -R -E > F:\QY\databak1.sql mysqldump -uroot -pmysql -h192.168.0.106 -P3306 -B pxzyy winlis -R -E > F:\QY\databak2.sql mysqldump -uroot -pmysql -h192.168.0.106 -P3306 -B dbsnmp dp_target mris neusoft neusoftlis ogg -R -E > F:\QY\databak4.sql mysqldump -uroot -pmysql -h192.168.0.106 -P3306 -B pacs31 pass px_his pxzyy_jsyb_2021 -R -E > F:\QY\databak5.sql |
2.2全库备份
1)备份所有数据库。(需要考虑编码问题)(记得排除系统表) Mysqldump -uroot -pmysql --all-databases --single-transaction --flush-logs --master-data=2 > all_databases.sql mysqldump -uroot -pmysql -h192.168.0.106 -P3306 -A -R -E --tables met_tec_inpatientconfirm > F:\his50QY\his403.sql linux下全库备份语句示范(xargs是在linux中适用) mysql -uroot -pmysql -h192.168.0.106 -P3306 --default-character-set=utf8 --master-data=2 -e "show databases"|grep -Ev "Database|information_schema|mysql|performance_schema|sys"|xargs mysqldump -uroot -pmysql -h192.168.0.106 -P3306 --default-character-set=utf8 --master-data=2 -R -E --databases > F:\mysqlbak\databak1.sql |
mysqldump -uroot -p --default-character-set=utf8 dbname > path/a.sql, 这样导出的数据就是正确的 |
以下方式不可以(也是第一次备份的文件) 输入命名mysqldump -uroot -pmysql -h192.168.0.106 -P3306 -A -R -E > F:\mysqlbak\databak.sql mysqldump -u${username} -p${password} -h${hostname或IP地址} -P${端口} -A -R -E > 存储路径/databak.sql |
本次备份对于his50库,一个库有290G,对于这种大文件,还需进行分表操作。本次采用分库分表压缩的方式进行备份。将导出的databak.sql文件上传到交换空间,然后上传到服务器。注意过程中使用XFTP速度最快,可以下载一个
备份过程中,以下报错均为本地与跳板机断开链接导致的
六、source数据库恢复,
思路:1、对于全库备份要先查一下max_allowed_packet够不够。2、对于多库(-B)备份的恢复无需建表。3、对于单库多表和单库单表恢复应该先创建数据库。然后指定后再恢复。
语句:登陆成功后再次执行
source /相对路径下的/XXX.sql ; |
source /data/databack.sql ; |
ps: 需要写对路径,可以写成绝对路径[c:\x\x 或者 /user/local/…]
如果导出的sql文件直接在mysql的bin目录下,可直接写相对路径
此处去刷新也可以看到数据存储位置发生了转变,说明正在导入
1.过程
1.2修改max_allowed_packet
首先为防止报错,先查max_allowed_packet
show variables like '%max_allowed_packet%';
select @@max_allowed_packet;
本次恢复的文件大小为300多G,超出max_allowed_packet
修改max_allowed_packet为1T
set global max_allowed_packet = 1024*1024*1024;
(这里的单位是字节)
2.恢复时报错
2.1种类一ASCII '\0'报错
意思就是ASCII '\0' 是不允许的,除非在binary mode下,原因在于一个存文本模式的sql脚本不可能存在'\0','\0'对应ASCII的00,NUL。
解决方案1:登陆mysql是,使用登陆语句为mysql --binary-mode=1 -uroot -p
解决方案2:把SQL文件在头加入sublime-file-save with encoding - utf-8 with bom,经过测试就可以了
但是文件太大无法打开,那么就追加到文件中去
head -10000 /data/mysql_qy /tmp.sql >/data/mysql_qy/ databak.sql
#把tmp.sql文件前10000行的数据写入到databak.sql文件中。
追加到首行(注意一定要有回车。即\)
sed -i '1i\
sublime-file-save with encoding - utf-8 with bom' /data/mysql_qy/ databak.sql
2.2种类二:导入异常结束,显示bye。
此时并非导入成功,形成原因
原因一:
极有可能是上传到数仓服务器的SQL文件不完整导致的,上传过程中,有事显示失败但是也会才在一个和本地SQL相同大小的SQL文件。
很明显数据只恢复了9个G便断掉了
2.3种类三: [Warning] IP address 'xxxx' could not be resolved: Name or service not known错误解决
查看日志
发现是因为mysql默认会反向解析DNS,对于访问者Mysql不会判断是hosts还是ip都会进行dns反向解析,频繁地查询数据库和权限检查,这大大增加了数据库的压力,导致数据库连接缓慢,严重的时候甚至死机。
解决办法:禁用dns反查即可。进入/etc 找到mysql的配置文件my.cnf(linux环境下)或者my.ini(windows环境下)进行编辑加入如下一行即可:
[mysqld]
skip-name-resolve
2.4种类四:Failed to open file '/data/databack.sql', error: 2
原因是错误来源是找不到其他sql文件
解决方法:①查看SQL路径的名字是否写错(本次报错是因为文件叫databak.sql 而不是叫databack.sql,多打一个c)。
②进入到文件保存的目录下,进入mysql,此时执行的命令为source databak.sql就可以执行。
③可能是某个进程占用了该文件所在的文件夹,解决方法:将该文件拷贝到其他文件夹中再试。
2.5种类五:报错NO database selectd
通过语句mysqldump -uroot -pmysql -h192.168.0.106 -P3306 mhis -R -E > F:\QY\mhis.sql
导出的SQL文件,恢复时会报错
原因是对于单独导出的库,需要先建库,再选用,再恢复数据,即可
一次恢复多个库时,不存在该种问题
2.6种类六:表锁
选用时报错,重新备份数据,去删除恢复的部分数据时报错,表锁
表锁导入数据出错,导入表不全,选用时报错
查看原因是因为1192-Can't execute the given command because you have active locked tables or an active transaction
解决办法:在Navicat中的hiss库新建查询执行unlock tables;再删除库即可
3.当找不到错误的时候应该
没有看到任何的错误。现在准备在mysql的配置文件中添加或者修改,vi /etc/my.cnf
log=/data/mysql_data/mysql_log/log.log #日志 |
slow-query-log_file=/data/mysql_data/mysql_log/slow-query-log.log #慢查询日志 |
log-error=/data/mysql_data/mysql_log/log-err.log #错误日志 |
log-queries-not-using-indexes = /data/mysql_data/mysql_log/nouseindex.log #不使用索引的日志查询 |
3.1修改完成后,重启mysql服务。报错无权限
解决方法:应该给mysql_data文件夹权限,给予mysql权限
chown -R mysql:mysql /data/mysql_data
3.2恢复全备份库后,报错密码不正确
恢复过程中出错,此时mysql也无法连接显示密码错误(经过查找对比,恢复的mysql节点密码变为了备份节点的密码)
3.2备份时未过滤系统库,但是数据上传完整么:
即使直接-A的去mysqldump,后续还是有解决的办法的。从本地通过堡垒机上传一个几百G的文件十分不容易。如果已经把错误格式的SQL文件上传成功了,为了避免资源浪费
可以在SQL文件的第一行加入
sed -i '1i\
sublime-file-save with encoding - utf-8 with bom' /data/mysql_qy/ databak.sql
(\注意此处是换行的意思)
加入此行如果会发现300多个G的文件变成了8个G
那么备份文件上传也是不完整的
综上所述:
根据日志查看得知,①备份文件上传服务器时不完整。②在备份数据时,原因是因为备份的时候没有过滤掉系统的四个库。③数据库密码改变。
实际生产过程中的问题梳理
1. mysql备份脚本编写
首先准备msyql备份目录
mkdir /data/mysql_backup
cd /data/mysql_backup
编写mysql备份脚本
vi mysql_backup_script.sh
按 i 进入编辑模式
下面是一个centos生产环境的数据库备份脚本,安排成crontab计划任务完成数据库自动备份:
#!/bin/sh
d=`date +%Y%m%d%H%M%S`.sql
/usr/local/mysql/bin/mysqldump -uroot -pZZYpeixian@2022 -h192.168.1.91 -P3306 -A -R -E | gzip > /data/mysql_backup/$d.gz
echo 'backup completed!'
Esc :wq 保存脚本
为脚本授权
chmod u+x mysql_backup_script.sh
测试脚本
[root@localhost mysql_backup] # sh ./mysql_backup_script.sh
mysqldump: [Warning] Using a password on the command line interface can be insecure.
adding: xxl_job_202206171607.sql (deflated 85%)
backup completed!
(返回此为正确)
以上方法报错,并没有备份数据,修改文本后
#!/bin/sh
d=`date +%Y%m%d%H%M%S`.sql
/usr/local/mysql/bin/mysql -e "show databases;" -uroot -pZZYpeixian -h192.168.1.91 -P3306 -ss | grep -Ev "information_schema|mysql|sys|performance_schema" | xargs /db/mysql/bin/mysqldump -uroot -pZZYpeixian@2022 -h192.168.1.91 -P3306 --databases | gzip >/data/mysql_backup/$d.gz
echo "susses"