【MySQL数据库疑难杂症】简单易操作升级方法-365彩票老版本-英国365bet日博-office365输入账号-365彩票老版本

MySQL 5.6.35升级到MySQL 5.7.201、dump一份数据作为备份（主要为备份，后面用不到）代码语言：bash复制[root@localhost mysql]# mysql -usamrsa -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 7

Server version: 5.5.54-log MySQL Community Server（GPL）

Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates.

Other names may be trademarks of their respective owners.

Type 'help; ' or '\h' for help. Type '\c' to clear the current input statement.

##加全局读锁

mysql> flush table with read lock;

Query OK, 0 rows affected（0.01 sec）

##设置为只读

mysql> set global read_only=ON;

Query OK, 0 rows affected（0.00 sec）

mysql> Ctrl-C -- exit！

Aborted[root@localhost local]#mysqldump -uroot-p --add-drop -table --routines-events --all-databases --force > /home/mysql/data/data-for-upgrade.sql

Enter password:

[root@localhost local]# vim /home/mysql/data/data-for-upgrade.sql

[root@localhost local]# service mysqld stop

Shutting down MySQL.. [ OK ]

[root@localhost local]# ll /home/mysql/data/data-for-upgrade.sql

-rw-r--r--1 root root 556738 Feb 13 19:31 /home/mysql/data/ data-for-upgrade.sql2、备份数据空间在升级前一定要备份与数据相关的所有文件，包括datadir、ib_logfile*、ibdata1和binlog；当升级发生意外时，可以通过备份迅速回滚升级操作。这里直接备份整个data目录。

代码语言：bash复制[root@localhost mysql]# cd /home/mysql/

[root@localhost mysql]# cp -ar data/ data.bak

[root@localhost mysql]# ll

total 28

drwxr-xr-x 2 mysql mysql 4096 Feb 13 17:30 conf

drwxr-xr-x 3 mysql mysql 4096 Dec 10 21:06 data

drwxr-xr-x 3 mysql mysql 4096 Dec 10 21:06 data.bak

drwxr-xr-x 4 mysql mysql 4096 Feb 13 17:01 program3、停止MySQL 5.5.35，并替换其basedir为MySQL 5.7.20的basedir，备份数据目录。停止MySQL 低版本，并替换其basedir为MySQL 高版本的basedir，备份数据目录。

代码语言：bash复制#这一步上面已经操作了

[root@localhost local]# service mysqld stop

Shutting down MySQL.. [ OK ]

# 查看MySQL是否停止成功

[root@localhost local]# ps aux |grep mysqld_safe

root 28775 0.0 0.0103252 844 pts/0 S+ 18:47 0:00 grep mysqld_safe

[root@localhost local]# netstat -ntupl |grep mysqld

tcp 0 0 :::9104 :::* LISTEN 1968/mysqld_exporte

# 解压缩MySQL 5.6.35二进制安装文件

[root@localhost mysql]# cd

[root@localhost ～]# ll

total 724992

drwxr-xr-x 2 root root 4096 Jan 29 2016 Desktop

drwxr-xr-x 2 root root 4096 Jan 29 2016 Documents

drwxr-xr-x 2 root root 4096 Jan 29 2016 Downloads

drwxr-xr-x 3 root root 4096 Jan 29 2016 install

-rw-r--r--. 1 root root 1971 Jan 29 2016 ks-post.log

-rw-r--r--. 1 root root 1111 Jan 29 2016 ks-pre.log

drwxr-xr-x 7 root root 4096 Dec 8 2015 MLNX_OFED_LINUX-3.1-1.1.0.1-rhel6.6-x86_64

-rw-r--r-- 1 root root 236676414 Jan 29 2016 MLNX_OFED_LINUX-3.1-1.1.0.1-rhel6.6-x86_64.tgz

drwxr-xr-x 2 root root 4096 Jan 29 2016 Music

-rw-r--r-- 1 root root 185911232 Feb 13 15:58 mysql-5.5.54-linux2.6-x86_64.tar.gz

-rw-r--r-- 1 root root 314581668 Jan 17 16:49 mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz

-rw-r--r-- 1 root root 5053796 May 13 2016 percona-xtrabackup-2.2.12-1.el6.x86_64.rpm

drwxr-xr-x 2 root root 4096 Jan 29 2016 Pictures

drwxr-xr-x 2 root root 4096 Jan 29 2016 Public

-rw-r--r-- 1 root root 95240 Feb 22 2016 rlwrap-0.42-1.el6.x86_64.rpm

drwxr-xr-x 2 root root 4096 Jan 29 2016 Templates

drwxr-xr-x 2 root root 4096 Jan 29 2016 Videos

[root@localhost ～]# tar xvf mysql-5.7.20-linux-glibc2.5- x86_64.tar.gz -C /home/mysql/program/

# 替换basedir

[root@localhost local]# cd /usr/local/

[root@localhost local]# ll

total 44

drwxr-xr-x. 2 root root 4096 Oct 27 17:54 bin

drwxr-xr-x. 2 root root 4096 Jun 28 2011 etc

drwxr-xr-x. 2 root root 4096 Jun 28 2011 games

drwxr-xr-x. 2 root root 4096 Jun 28 2011 include

drwxr-xr-x. 2 root root 4096 Jun 28 2011 lib

drwxr-xr-x. 2 root root 4096 Jun 28 2011 lib64

drwxr-xr-x. 2 root root 4096 Jun 28 2011 libexec

lrwxrwxrwx 1 root root 49 Feb 13 18:04 mysql -> /home/mysql/program/mysql-5.7.20-linux2.6-x86_64/

drwxr-xr-x 3 root root 4096 Jan 16 14:16 qflame

drwxr-xr-x. 2 root root 4096 Jun 28 2011 sbin

drwxr-xr-x. 5 root root 4096 Jan 29 2016 share

drwxr-xr-x. 2 root root 4096 Jun 28 2011 src

[root@localhost local]# unlink mysql

[root@localhost local]#ln-s/home/mysql/program/mysql-5.7.20-linux-glibc2.5-x86_64/usr/local/mysql

[root@localhost local]# ll

total 44

drwxr-xr-x. 2 root root 4096 Oct 27 17:54 bin

drwxr-xr-x. 2 root root 4096 Jun 28 2011 etc

drwxr-xr-x. 2 root root 4096 Jun 28 2011 games

drwxr-xr-x. 2 root root 4096 Jun 28 2011 include

drwxr-xr-x. 2 root root 4096 Jun 28 2011 lib

drwxr-xr-x. 2 root root 4096 Jun 28 2011 lib64

drwxr-xr-x. 2 root root 4096 Jun 28 2011 libexec

lrwxrwxrwx 1 root root 55 Feb 13 18:46 mysql -> /home/mysql/program/mysql-5.7.20-linux-glibc2.5-x86_64/

drwxr-xr-x 3 root root 4096 Jan 16 14:16 qflame

drwxr-xr-x. 2 root root 4096 Jun 28 2011 sbin

drwxr-xr-x. 5 root root 4096 Jan 29 2016 share

drwxr-xr-x. 2 root root 4096 Jun 28 2011 src

# 备份数据目录

[root@localhost local]# cd /home/mysql/

[root@localhost mysql]# ll

total 24

drwxr-xr-x 2 mysql mysql 4096 Feb 13 17:36 conf

drwxr-xr-x 3 mysql mysql 4096 Feb 13 18:32 data

drwxr-xr-x 4 mysql mysql 4096 Feb 13 17:01 program

[root@localhost mysql]# cp -ar data/ data.bak

[root@localhost mysql]# ll

total 28

drwxr-xr-x 2 mysql mysql 4096 Feb 13 17:36 conf

drwxr-xr-x 3 mysql mysql 4096 Feb 13 18:32 data

drwxr-xr-x 3 mysql mysql 4096 Feb 13 18:32 data.bak

drwxr-xr-x 4 mysql mysql 4096 Feb 13 17:01 program

[root@localhost mysql]# cd data/mysqldata1/

[root@localhost mysqldata1]# ll

total 36

drwxr-xr-x 2 mysql mysql 4096 Feb 13 18:06 binlog

drwxr-xr-x 2 mysql mysql 4096 Feb 13 18:06 innodb_log

drwxr-xr-x 2 mysql mysql 4096 Feb 13 18:06 innodb_ts

drwxr-xr-x 2 mysql mysql 4096 Feb 13 18:06 log

drwxr-xr-x 5 mysql mysql 4096 Feb 13 18:31 mydata

drwxr-xr-x 2 mysql mysql 4096 Feb 13 18:06 slowlog

drwxr-xr-x 2 mysql mysql 4096 Feb 13 18:46 sock

drwxr-xr-x 2 mysql mysql 4096 Feb 13 18:38 tmpdir

drwxr-xr-x 2 mysql mysql 4096 Feb 13 16:08 undo

drwxr-xr-x 2 mysql mysql 4096 Feb 13 16:08 relaylog

# 清理MySQL 5.5.54的数据目录

[root@localhost mysqldata1]# rm -rf {binlog,innodb_log,innodb_ts,log,mydata,slowlog,sock,tmpdir,undo}/*

[root@localhost mysqldata1]# tree .

├—— binlog

├—— innodb_log

├—— innodb_ts

├—— log

├—— mydata

├—— slowlog

├—— sock

├—— tmpdir

└—— undo

└—— relaylog

9 directories, 0 files3、初始化数据库代码语言：bash复制# 使用该选项初始化时会在错误日志中写一个随机的root密码，初始化完成之后在错误日志中搜索password

[root@localhost mysql]# mysqld --defaults-file=/etc/my.cnf --initialize

#使用该选项初始化时不会产生随机密码，而是像MySQL 5.7之前的版本一样，初始化完成之后，第一次登录数据库使用空的root密码

[root@localhost mysql]# mysqld --defaults-file=/etc/my.cnf --initialize-insecure初始化可能会报的错误，下面解决办法

问题 1

mysqld: error while loading shared libraries: libnuma.so.1: cannot open shared object file: No such file or directory

解决办法

代码语言：bash复制[root@localhost mysql]#sudo yum install numactl-libs

# 能装上就ok了，装不上进行下面的

[root@localhost mysql]#ldconfig -p | grep libnuma

#找不到

[root@localhost mysql]#sudo yum makecache

[root@localhost mysql]#sudo yum install numactl-libs

[root@localhost mysql]#sudo yum search numa

================= N/S Matched: numa ===========================

numactl-devel.i686 : Development package for building Applications that use numa

numactl-devel.x86_64 : Development package for building Applications that use numa

numad.x86_64 : NUMA user daemon

numactl.i686 : Library for tuning for Non Uniform Memory Access machines

numactl.x86_64 : Library for tuning for Non Uniform Memory Access machines

numatop.x86_64 : Memory access locality characterization and analysis

#找到自己需要的安装

[root@localhost mysql]#sudo yum install numactl.x86_64

# 安装完验证

[root@hadoop01 mysql]# ldconfig -p | grep libnuma

libnuma.so.1 (libc6,x86-64) => /usr/lib64/libnuma.so.1

[root@hadoop01 mysql]# mysqld --defaults-file=/etc/my.cnf --initialize-insecure

2025-02-09T18:19:06.653628Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.

***问题2

Starting MySQL.2025-02-09T18:23:24.983395Z mysqld_safe error: log-error set to '/home/mysql/data/mysqldata1/log/error.log', however file don't exists. Create writable for user 'mysql'.

ERROR! The server quit without updating PID file (/home/mysql/data/mysqldata1/sock/mysql.pid).

解决办法

代码语言：bash复制mkdir -p /home/mysql/data/mysqldata1/log/

chown -R mysql:mysql /home/mysql/data/mysqldata1/log/

chmod -R 750 /home/mysql/data/mysqldata1/log/

touch /home/mysql/data/mysqldata1/log/error.log

chown mysql:mysql /home/mysql/data/mysqldata1/log/error.log

chmod 640 /home/mysql/data/mysqldata1/log/error.log4、停止数据库代码语言：bash复制service mysqld status

service mysqld stop5、替换数据库文件代码语言：bash复制[root@hadoop01 mysql]# ls

conf data data.bak data.bak2 program

[root@hadoop01 mysql]# pwd

/home/mysql

[root@hadoop01 mysql]# sudo mv /home/mysql/data /home/mysql/data.old

[root@hadoop01 mysql]# sudo cp -ar /home/mysql/data.bak /home/mysql/data

[root@hadoop01 mysql]# sudo chmod -R 750 /home/mysql/data

[root@hadoop01 mysql]# sudo service mysqld start

Starting MySQL.. SUCCESS!

[root@hadoop01 mysql]# mysql -u root -p

*****启动正常

6、测试数据库信息代码语言：bash复制mysql> select user();

+-------------------+

| user() |

+-------------------+

| program@localhost |

+-------------------+

1 row in set（0.00 sec）

# 查看升级之后的版本号

mysql> select version();

+------------+

| version() |

+------------+

| 5.7.20-log |

+------------+

1 row in set（0.00 sec）

# 查看程序用户权限

mysql> show grants;

+---------------------------------------------------+

| Grants for program@localhost |

+---------------------------------------------------+

| GRANT USAGE ON *.* TO 'program'@'localhost' IDENTIFIED BY PASSWORD |

| GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON`samrsa_db`.* TO 'program'@'localhost' |

+---------------------------------------------------+

2 rows in set（0.00 sec）

# 访问用户数据

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| samrsa_db |

+--------------------+

2 rows in set（0.01 sec）

mysql> use samrsa_db

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+-----------------------+

| Tables_in_samrsa_db |

+-----------------------+

| samrsa_table |

+-----------------------+

1 row in set（0.00 sec）

mysql> select * from samrsa_table;

+----+---------------+---------------------+

| id | samrsa_test | datetime_current |

+----+---------------+---------------------+

| 1 | samrsa | 2025-02-08 14:50:32 |

+----+---------------+---------------------+

1 row in set（0.00 sec）

mysql> insert into samrsa_table(samrsa_test, datetime_current)values('samrsa', now());

Query OK, 1 row affected（0.01 sec）

mysql> select * from samrsa_table;

+----+---------------+---------------------+

| id | samrsa_test | datetime_current |

+----+---------------+---------------------+

| 1 | samrsa | 2025-02-08 14:50:32 |

| 2 | samrsa | 2025-02-08 15:20:12 |

+----+---------------+---------------------+

2 rows in set（0.00 sec）

【MySQL数据库疑难杂症】简单易操作升级方法

相关推荐

王处士凿山引瀑记中华文库

王動WANIMAL – 有龍台灣首個展

[足球]葡萄牙世界杯if线

微博打开卡包的方法步骤

热血物语双人怎么设置

删除 Word 空白页的 4 种方法总结

ps怎么快速把图层变成选区

会计记账软件哪个好用？2025年老会计实测拆解

军阀的意思、解释和含义

《暴风影音》关闭弹幕方法介绍

“棒子”这词的来历，多少都带着不同地方对韩国的歧视和蔑视，但都源自于民间传说

央视不直播！中国国足盼10年首胜，冲击世预赛附加赛

合作伙伴