mysql数据库自动降级_mysql软件升降级及配置参数

1、下载mysql5.7的软件

https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

2、解压mysql-5.7.20到/usr/local/

[root@mysqlmaster01 ~]# tar xf /software/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz -C /usr/local/

3、正常关闭mysql数据库

[root@mysqlmaster01 ~]# /etc/init.d/mysqld stop

4、拷贝数据目录下的mysql数据库、然后备份重要的数据库文件

[root@mysqlmaster01 mysql_data]# cp -r mysql mysql.old

5、然后进入到/usr/local/目录，把原来的mysql5.6的链接unlink掉

[root@mysqlmaster01 mysql_data]# cd /usr/local/

[root@mysqlmaster01 local]# unlink mysql

[root@mysqlmaster01 local]#ln -sv mysql-5.7.20-linux-glibc2.12-x86_64/ mysql

然后启动mysqld服务

[root@mysqlmaster01 local]#/etc/init.d/mysqld start

6、运行mysql_upgrade

[root@mysqlmaster01 mysql]# bin/mysql_upgrade -s -p输入root密码

-s, --upgrade-system-tables  (仅仅更新系统表)

Only upgrade the system tables, do not try to upgrade the

-f, --force Force execution of SQL statements even if mysql_upgrade

has already been executed for the current version of

mysql版本降级

官网：https://dev.mysql.com/doc/refman/5.7/en/downgrading.html#downgrade-methods

第一种方式: 逻辑降级

1)Dump all databases. For example:

[root@mysqlmaster01 ~]# mysqldump -u root -p --add-drop-table --routines --events --all-databases --force > /root/downgrade.sql

Enter password:

[root@mysqlmaster01 ~]# ll /root/downgrade.sql

-rw-r--r--. 1 root root 661169 Nov 22 09:50 /root/downgrade.sql

2)Shut down the newer MySQL server.

[root@mysqlmaster01 ~]# service mysqld stop

Shutting down MySQL.... SUCCESS!

3)初始化一个旧的MySQL实例，并使用一个新的数据目录。例如，要初始化一个MySQL 5.6实例，使用mysql_install_db:

下载mysql版本的二进制文件，然后重新链接mysql到旧的mysql版本上

[root@mysqlmaster01 ~]#/data/mysql5.5_data

[root@mysqlmaster01 ~]# cd /usr/local/mysql

[root@mysqlmaster01 mysql]# scripts/mysql_install_db --user=mysql --datadir=/data/mysql5.5_data

如果初始化5.7的版本，则use mysqld with the --initialize

[root@mysqlmaster01 ~]# mkdir /data/mysql5.6_data

[root@mysqlmaster01 ~]# chown -R mysql.mysql /data/mysql5.6_data/

[root@mysqlmaster01 ~]# cd /usr/local/

[root@mysqlmaster01 local]# ll

total 48

drwxr-xr-x. 2 root root 4096 Sep 23 2011 bin

drwxr-xr-x. 2 root root 4096 Sep 23 2011 etc

drwxr-xr-x. 2 root root 4096 Sep 23 2011 games

drwxr-xr-x. 2 root root 4096 Sep 23 2011 include

drwxr-xr-x. 2 root root 4096 Sep 23 2011 lib

drwxr-xr-x. 2 root root 4096 Sep 23 2011 lib64

drwxr-xr-x. 2 root root 4096 Sep 23 2011 libexec

lrwxrwxrwx. 1 root root 36 Nov 21 16:15 mysql -> mysql-5.7.20-linux-glibc2.12-x86_64/

drwxr-xr-x. 13 root mysql 4096 Nov 21 15:37 mysql-5.6.38-linux-glibc2.12-x86_64

drwxr-xr-x. 9 root root 4096 Nov 21 16:14 mysql-5.7.20-linux-glibc2.12-x86_64

drwxr-xr-x. 2 root root 4096 Sep 23 2011 sbin

drwxr-xr-x. 5 root root 4096 Aug 4 09:54 share

drwxr-xr-x. 2 root root 4096 Sep 23 2011 src

[root@mysqlmaster01 local]# unlink mysql

[root@mysqlmaster01 local]# ln -sv mysql-5.6.38-linux-glibc2.12-x86_64/ mysql

`mysql' -> `mysql-5.6.38-linux-glibc2.12-x86_64/'

4)Start the older MySQL server, using the new data directory

把新的my.cnf配置文件配置一份，然后修改一下my.cnf中的datadir目录指向旧的数据目录，然后执行初始化操作

[root@mysqlmaster01 local]# vim /etc/my.cnf

[client]

#password = your_password

port = 3306

socket = /tmp/mysql.sock

[mysqld]

port = 3306

socket = /tmp/mysql.sock

skip-external-locking

key_buffer_size = 256M

max_allowed_packet = 1M

table_open_cache = 256

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

thread_cache_size = 2

query_cache_size= 16M

datadir=/data/mysql5.6_data/

innodb_log_file_size = 1G

log_error = error.log

skip_name_resolve = 1

bind_address = 10.2.11.226

~

[root@mysqlmaster01 mysql]# scripts/mysql_install_db --user=mysql --datadir=/data/mysql5.6_data/   初始化mysql数据库到新的目录

2017-11-22 10:06:57 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.

2017-11-22 10:06:57 0 [Note] ./bin/mysqld (mysqld 5.6.38) starting as process 3353 ...

OK

Filling help tables...2017-11-22 10:08:27 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2017-11-22 10:08:27 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.

2017-11-22 10:08:27 0 [Note] ./bin/mysqld (mysqld 5.6.38) starting as process 3375 ...

OK

[root@mysqlmaster01 bin]# ./mysqld_safe --user=mysql --datadir=/data/mysql5.6_data/ &

[3] 5970

[root@mysqlmaster01 bin]# 171122 10:31:02 mysqld_safe Logging to '/data/mysql5.6_data/error.log'.

171122 10:31:02 mysqld_safe A mysqld process already exists

[3] Exit 1 ./mysqld_safe --user=mysql --datadir=/data/mysql5.6_data/

[root@mysqlmaster01 bin]#

[root@mysqlmaster01 bin]# ss -tunlp|grep 3306

tcp LISTEN 0 128 10.2.11.226:3306 *:* users:(("mysqld",5945,22))

[root@mysqlmaster01 bin]# mysql -V

mysql Ver 14.14 Distrib 5.6.38, for linux-glibc2.12 (x86_64) using EditLine wrapper

如果运行mysql -u root 提示要输入密码，操作如下

先正常关闭mysqld服务，然后运行时，加--skip-grant-tables

[root@mysqlmaster01 bin]# ./mysqld_safe --user=mysql --datadir=/data/mysql5.6_data/ --skip-grant-tables &

mysql> update mysql.user set password=PASSWORD('Aa123456') where User='root';

Query OK, 0 rows affected (0.01 sec)

Rows matched: 0 Changed: 0 Warnings: 0

mysql> flush privileges;

Query OK, 0 rows affected (5.01 sec)

然后重启mysql

[root@mysqlmaster01 bin]# service mysqld start

Starting MySQL SUCCESS!

[root@mysqlmaster01 bin]# ss -tunlp|grep 3306

[root@mysqlmaster01 bin]#

[root@mysqlmaster01 bin]#

[root@mysqlmaster01 bin]# ss -tunlp|grep 3306

tcp LISTEN 0 128 10.2.11.226:3306 *:* users:(("mysqld",22339,22))

5)Load the dump file into the older MySQL server. For example:

[root@mysqlmaster01 bin]# ./mysql -u root -p < /root/downgrade.sql

Enter password:

ERROR 1726 (HY000) at line 202: Storage engine 'InnoDB' does not support system tables. [mysql.help_category]

6)Run mysql_upgrade. For example:

mysql_upgrade -u root -p

7)重新启动mysqld数据库

二、mysql_config_editor命令

[root@mysqlmaster01 mysql]# mysql_config_editor --help

mysql_config_editor Ver 1.0 Distrib 5.7.20, for linux-glibc2.12 on x86_64

Copyright (c) 2012, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

MySQL Configuration Utility.

Usage: mysql_config_editor [program options] [command [command options]]

-#, --debug[=#] This is a non-debug version. Catch this and exit.

-?, --help Display this help and exit.

-v, --verbose Write more information.

-V, --version Output version information and exit.

Variables (--variable-name=value)

and boolean options {FALSE|TRUE} Value (after reading options)

--------------------------------- ----------------------------------------

verbose FALSE

Where command can be any one of the following :

set [command options] Sets user name/password/host name/socket/port

for a given login path (section).

remove [command options] Remove a login path from the login file.

print [command options] Print all the options for a specified

login path.

reset [command options] Deletes the contents of the login file.

help Display this usage/help information.

[root@mysqlmaster01 mysql]# mysql_config_editor set -G vm1 -S /tmp/mysql.sock -uroot -p (新建一个vm1的标签)

Enter password:

[root@mysqlmaster01 mysql]# mysql_config_editor print --all (查看)

[vm1]

user = root

password = *****

socket = /tmp/mysql.sock

[root@mysqlmaster01 mysql]# mysql --help -vv|grep login

-u, --user=name User for login if not current user.

except for login file.

--login-path=# Read this path from the login file.

-rw-------. 1 root root 136 Nov 21 16:59 .mylogin.cnf (在root目录下会产生这个文件)

[root@mysqlmaster01 mysql]# mysql --login-path=vm1 (通过这种方式，不用输入密码)

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

Your MySQL connection id is 7

Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

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

mysql>

三)mysql配置参数

从作用域上可以分为global和session

从类型上可分为可修改和不可修改

用户可以在线修改非只读参数

只读参数只能通过配置文件修改并重启mysqld

所有参数的修改都不是持久化的，服务器重启后失效，如果要永久生效，必须写到配置文件中

查看变量：

mysql> show global variables;

mysql> show global variableslike 'slow%'; (like来进行匹配)

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

| Variable_name | Value |

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

| slow_launch_time | 2 |

| slow_query_log | ON |

| slow_query_log_file | /data/mysql_data/mysqlmaster01-slow.log |

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

3 rows in set (0.00 sec)

(每个版本的，参数基本都不一样)

mysql> set global slow_query_log = 'OFF'; (修改mysql配置参数，通过set命令)

Query OK, 0 rows affected (0.00 sec)

四)mysql用户权限问题

注意：删除所有用户名为空的用户，不允许密码为空的用户存在，管理员可以管理所有库的权限，开发只给响应库的权限

根据用户名和IP来进行过滤

查看mysql.user、mysql.db、mysql.tables_priv

mysql> show tables;

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

| Tables_in_mysql |

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

| columns_priv |

| db |

| engine_cost |

| event |

| func |

| general_log |

| gtid_executed |

| help_category |

| help_keyword |

| help_relation |

| help_topic |

| innodb_index_stats |

| innodb_table_stats |

| ndb_binlog_index |

| plugin |

| proc |

| procs_priv |

| proxies_priv |

| server_cost |

| servers |

| slave_master_info |

| slave_relay_log_info |

| slave_worker_info |

| slow_log |

| tables_priv |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

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

31 rows in set (0.00 sec)

mysql> select * from user where user='root' limit 1 \G

*************************** 1. row ***************************

Host: localhost

User: root

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Reload_priv: Y

Shutdown_priv: Y

Process_priv: Y

File_priv: Y

Grant_priv: Y

References_priv: Y

Index_priv: Y

Alter_priv: Y

Show_db_priv: Y

Super_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Execute_priv: Y

Repl_slave_priv: Y

Repl_client_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Create_user_priv: Y

Event_priv: Y

Trigger_priv: Y

Create_tablespace_priv: Y

ssl_type:

ssl_cipher:

x509_issuer:

x509_subject:

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin: mysql_native_password

authentication_string: *4A488726AE5A0B0F0DB967998EE12D87F25C9610

password_expired: N

password_last_changed: 2017-11-21 16:21:46

password_lifetime: NULL

account_locked: N

1 row in set (0.00 sec)

常用权限规划

SQL语句： select 、insert、update、delete、 index

存储过程： create routine、alter routine、execule

管理权限： super、reload、show database、shutdown、create database

官网：https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html (查看mysqld5.7有哪些些权限)

mysql> help grant

Name: 'GRANT'

Description:

Syntax:

GRANT

priv_type [(column_list)]

[, priv_type [(column_list)]] ...

ON [object_type] priv_level

TO user [auth_option] [, user [auth_option]] ...

[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]

[WITH {GRANT OPTION | resource_option} ...]

GRANT PROXY ON user

TO user [, user] ...

[WITH GRANT OPTION]

object_type: {

TABLE

| FUNCTION

| PROCEDURE

}

priv_level: {

*

| *.*

| db_name.*

| db_name.tbl_name

| tbl_name

| db_name.routine_name

}

user:

(see http://dev.mysql.com/doc/refman/5.6/en/account-names.html)

auth_option: {

IDENTIFIED BY 'auth_string'

| IDENTIFIED BY PASSWORD 'hash_string'

| IDENTIFIED WITH auth_plugin

| IDENTIFIED WITH auth_plugin AS 'hash_string'

}

tls_option: {

SSL

| X509

| CIPHER 'cipher'

| ISSUER 'issuer'

| SUBJECT 'subject'

}

resource_option: {

| MAX_QUERIES_PER_HOUR count

| MAX_UPDATES_PER_HOUR count

| MAX_CONNECTIONS_PER_HOUR count

| MAX_USER_CONNECTIONS count

}

用户权限授权

官网：https://dev.mysql.com/doc/refman/5.7/en/grant.html

mysql> grant all on *.* to 'senior_dba'@'127.0.0.1' identified by 'Aa123456' with grant option;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

说明：授权senior_dba从127.0.0.1登录，拥有所有权限，并且该用户还可以授权其他用户

[root@mysqlmaster01 ~]# mysql -u senior_dba -p

Enter password:

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

Your MySQL connection id is 12

Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

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

mysql> grant select,insert,update,delete on wpdb.* to 'dev'@'10.2.18.%' identified by '123456';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant select on wpdb.wp_report to 'report'@'10.2.18.%' identified by '123456';

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

错误1：

在升级MySQL5.5过程中，执行/usr/local/mysql/bin/mysql_upgrade

提示：

Looking for 'mysql' in: /usr/local/mysql/bin/mysql

Looking for 'mysqlcheck' in: /usr/local/mysql/bin/mysqlcheck

Running 'mysqlcheck'...

/usr/local/mysql/bin/mysqlcheck: Got error: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOR UPGRADE' at line 1 when executing 'CHECK TABLE ... FOR UPGRADE'

FATAL ERROR: Upgrade failed

启用 --skip-grant-tables参数避开检查，先启动再说

/usr/local/mysql/bin/mysqld --datadir=/usr/local/mysql/var/ --user=mysql --skip-grant-tables &

然后配置my.cnf参数，以适合新版本的