MYSQL-mysql版本升级到5.7并迁移数据

柔光的暖阳◎ 2022-12-05 01:39 281阅读 0赞

### 文章目录 ###

*  MYSQL-mysql版本升级到5.7并迁移数据
 *   *  1. 安装mariadb数据库，并测试登录
     *  2.查看test数据库并备份数据
     *  3. 卸载mariadb数据库
     *  4. 安装高版本mysql数据库
     *  5. 恢复数据

# MYSQL-mysql版本升级到5.7并迁移数据 #

## 1. 安装mariadb数据库，并测试登录 ##

[root@server ~]# yum install -y mariadb*
    [root@server ~]# systemctl start mariadb.service
    [root@server ~]# systemctl enable mariadb.service 
    Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
    
    [root@server ~]# mysql # 刚安装mariadb数据库，所有账号都是没有密码的。可以匿名登录进去的。
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 6
    Server version: 5.5.60-MariaDB MariaDB Server
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MariaDB [(none)]> show databases;   # 显示有哪些数据库
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    +--------------------+
    4 rows in set (0.00 sec)
    
    MariaDB [(none)]> use mysql;        # 切换到mysql数据库
    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
    MariaDB [mysql]> select host,user,password from user;       # mysql存放数据库用户和权限信息，显示数据库用户信息。
    +-----------+------+----------+
    | host      | user | password |
    +-----------+------+----------+
    | localhost | root |          |
    | server    | root |          |
    | 127.0.0.1 | root |          |
    | ::1       | root |          |
    | localhost |      |          |
    | server    |      |          |
    +-----------+------+----------+
    6 rows in set (0.00 sec)
    
    MariaDB [mysql]> set password for root@localhost = password('123456');    # 设置root@localhost用户的密码
    Query OK, 0 rows affected (0.00 sec)
    
    MariaDB [mysql]> flush privileges;                    # 刷新权限
    Query OK, 0 rows affected (0.00 sec)
    
    MariaDB [mysql]> select host,user,password from user;      # 可以看到root用户是有密码的了
    +-----------+------+-------------------------------------------+
    | host      | user | password                                  |
    +-----------+------+-------------------------------------------+
    | localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
    | server    | root |                                           |
    | 127.0.0.1 | root |                                           |
    | ::1       | root |                                           |
    | localhost |      |                                           |
    | server    |      |                                           |
    +-----------+------+-------------------------------------------+
    6 rows in set (0.00 sec)
    
    [root@server ~]# mysql -uroot -p123456 # 这时可以使用root用户登录
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 7
    Server version: 5.5.60-MariaDB MariaDB Server
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MariaDB [(none)]> select user();          # 可以看到当前登录用户是root@localhost
    +----------------+
    | user()         |
    +----------------+
    | root@localhost |
    +----------------+
    1 row in set (0.00 sec)
    
    MariaDB [(none)]> exit
    Bye
    [root@server ~]# mysql # 可以看到，已经不能匿名登录了。
    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

## 2.查看test数据库并备份数据 ##

**在test数据库下创建一个表student：**

[root@server ~]# mysql -uroot -p123456
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 10
    Server version: 5.5.60-MariaDB MariaDB Server
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MariaDB [(none)]> use test;
    Database changed
    MariaDB [test]> show tables;
    Empty set (0.00 sec)
    MariaDB [test]> create table student(
        -> id int not null,
        -> name char(10) null,
        -> grade char(10) null,
        -> primary key(id));
    Query OK, 0 rows affected (0.00 sec)
    
    ======================================================================
    create table student( id int not null,name char(10) null,grade char(10) null,primary key(id));
    =======================================================================
    
    MariaDB [test]> select * from student;
    Empty set (0.00 sec)

**插入数据：**

MariaDB [test]> insert into student values(1,'a',10);
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [test]> insert into student values(2,'b',20);
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [test]> select * from student;
    +----+------+-------+
    | id | name | grade |
    +----+------+-------+
    |  1 | a    | 10    |
    |  2 | b    | 20    |
    +----+------+-------+
    2 rows in set (0.00 sec)
    
    MariaDB [test]> exit    # mysql数据库中，DDL语句是自动提交事务的，而oracle数据库是需要使用commit语句提交事务的。
    Bye
    [root@server ~]# mysql -uroot -p123456
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 11
    Server version: 5.5.60-MariaDB MariaDB Server
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MariaDB [(none)]> use test;
    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
    MariaDB [test]> select * from student;
    +----+------+-------+
    | id | name | grade |
    +----+------+-------+
    |  1 | a    | 10    |
    |  2 | b    | 20    |
    +----+------+-------+
    2 rows in set (0.00 sec)

**备份test数据库：**

[root@server ~]# mysqldump -uroot -p123456 -B test > test.sql
    [root@server ~]# ll test.sql 
    -rw-r--r--. 1 root root 2059 9月   8 20:44 test.sql

## 3. 卸载mariadb数据库 ##

[root@server ~]# yum remove mariadb* -y
    [root@server ~]# find / -name mysql
    /etc/selinux/targeted/active/modules/100/mysql
    /var/lib/mysql
    /var/lib/mysql/mysql
    /usr/lib64/mysql
    [root@server ~]# rm -rf /var/lib/mysql # 删除mysql数据库的默认数据文档存储目录，否则在安装mysql时会自动使用该目录。
    [root@server ~]# find / -name mysql
    /etc/selinux/targeted/active/modules/100/mysql
    /usr/lib64/mysql

## 4. 安装高版本mysql数据库 ##

[root@server ~]# rpm -ivh https://repo.mysql.com//yum/mysql-5.7-community/el/7/x86_64/mysql57-community-release-el7-10.noarch.rpm
    [root@server ~]# ls /etc/yum.repos.d/
    centos7_local.repo  mysql-community.repo  mysql-community-source.repo
    [root@server ~]# yum list # 刷新yum源缓存
    [root@server ~]# yum install mysql-community-server.x86_64 -y
    
    # 可以看到mysql数据库已经正常启动了
    [root@server ~]# systemctl start mysqld
    [root@server ~]# systemctl enable mysqld
    [root@server ~]# netstat -antup | grep mysqld
    tcp6       0      0 :::3306                 :::*                    LISTEN      78606/mysqld    
    [root@server ~]# ll /var/lib/mysql/ # 在启动mysql数据库后，可以看到已经初始化成功了。
    总用量 122952
    -rw-r-----. 1 mysql mysql       56 9月   8 22:18 auto.cnf
    -rw-------. 1 mysql mysql     1680 9月   8 22:18 ca-key.pem
    -rw-r--r--. 1 mysql mysql     1112 9月   8 22:18 ca.pem
    -rw-r--r--. 1 mysql mysql     1112 9月   8 22:18 client-cert.pem
    -rw-------. 1 mysql mysql     1680 9月   8 22:18 client-key.pem
    -rw-r-----. 1 mysql mysql      431 9月   8 22:18 ib_buffer_pool
    -rw-r-----. 1 mysql mysql 12582912 9月   8 22:18 ibdata1
    -rw-r-----. 1 mysql mysql 50331648 9月   8 22:18 ib_logfile0
    -rw-r-----. 1 mysql mysql 50331648 9月   8 22:18 ib_logfile1
    -rw-r-----. 1 mysql mysql 12582912 9月   8 22:18 ibtmp1
    drwxr-x---. 2 mysql mysql     4096 9月   8 22:18 mysql
    srwxrwxrwx. 1 mysql mysql        0 9月   8 22:18 mysql.sock
    -rw-------. 1 mysql mysql        6 9月   8 22:18 mysql.sock.lock
    drwxr-x---. 2 mysql mysql     8192 9月   8 22:18 performance_schema
    -rw-------. 1 mysql mysql     1676 9月   8 22:18 private_key.pem
    -rw-r--r--. 1 mysql mysql      452 9月   8 22:18 public_key.pem
    -rw-r--r--. 1 mysql mysql     1112 9月   8 22:18 server-cert.pem
    -rw-------. 1 mysql mysql     1676 9月   8 22:18 server-key.pem
    drwxr-x---. 2 mysql mysql     8192 9月   8 22:18 sys
    
    
    # 官方源比较慢的话可以使用清华大学的网络yum源：
    清华源：https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/
    [root@server ~]# cat /etc/yum.repos.d/mysql.repo 
    [mysql]
    name=mysql
    baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/
    enable=1
    gpgcheck=0
    [root@server ~]# yum list
    [root@server ~]# yum install -y mysql-community-server-5.7.32-1.el7.x86_64
    [root@server ~]# systemctl start mysqld
    [root@server ~]# systemctl enable mysqld
    [root@server ~]# netstat -antup | grep mysqld
    tcp6       0      0 :::3306                 :::*                    LISTEN      19628/mysqld

**登录mysql数据库：**

# mysql数据库默认不允许匿名登录
    [root@server ~]# mysql
    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
    
    # 查看日志获取root用户的临时密码
    # 临时密码：Ov3Ppf+hRLad
    [root@server ~]# cat /var/log/mysqld.log | grep password
    2020-09-08T14:18:41.375545Z 1 [Note] A temporary password is generated for root@localhost: Ov3Ppf+hRLad
    2020-09-08T14:36:35.431729Z 2 [Note] Access denied for user 'root'@'localhost' (using password: NO)
    
    
    [root@server ~]# mysql -uroot -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 3
    Server version: 5.7.31
    
    Copyright (c) 2000, 2020, 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> 
    # 修改root@localhost密码
    # 要有大小写字母、数字、特殊字符，长度8位及以上。
    mysql> set password for root@localhost=password('Abong123.');
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> exit
    Bye
    
    
    # 测试登录，可以看到是可以使用密码登录了。
    [root@server ~]# mysql -uroot -pAbong123.
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 8
    Server version: 5.7.31 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2020, 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>

## 5. 恢复数据 ##

# 由于test.sql文件的所有者和所属组为mysql，故要在/var/lib/mysql目录下上传文件。
    [root@server ~]# cd /var/lib/mysql
    [root@server mysql]# rz
    [root@server mysql]# ll test.sql 
    -rw-r--r--. 1 root root 2059 9月   8 21:46 test.sql
    
    # 登录mysql数据库，查看下当前有哪些数据库，可以看到没有test数据库，故可以导入。
    [root@server ~]# mysql -uroot -pAbong123.
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 9
    Server version: 5.7.31 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2020, 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> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    4 rows in set (0.01 sec)
    
    
    # 根据sql文件导入数据库
    # 有一个警告，是说在命令行使用明文密码是不安全的，不影响导入。
    [root@server ~]# mysql -uroot -pAbong123. < /var/lib/mysql/test.sql
    mysql: [Warning] Using a password on the command line interface can be insecure.
    
    # 查看数据恢复情况，可以看到数据库以及数据表都已经恢复回来了。
    [root@server ~]# mysql -uroot -pAbong123.
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 11
    Server version: 5.7.31 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2020, 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> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    | test               |
    +--------------------+
    5 rows in set (0.00 sec)
    
    mysql> use test;
    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_test |
    +----------------+
    | student        |
    +----------------+
    1 row in set (0.00 sec)
    
    mysql> select * from student;
    +----+------+-------+
    | id | name | grade |
    +----+------+-------+
    |  1 | a    | 10    |
    |  2 | b    | 20    |
    +----+------+-------+
    2 rows in set (0.01 sec)
    
    mysql> exit
    Bye

以上就是mysql数据库迁移以及版本升级的全过程了，无非就是**备份数据，然后卸载重装mysql数据库，最后恢复数据**。  
**为了方便以后的学习，可以打个快照。**