数据库迁移

网站服务器操作：

第一步:把最新的数据库备份到一个文件中

[root@web01 ~]# mysqldump  -uroot  -p -A |gzip   >/root/all.sql.gz

第二步：检查

[root@web01 ~]# zcat   /root/all.sql.gz 

第三步：把备份数据文件推到数据库服务器

[root@web01 ~]# scp  /root/all.sql.gz  172.16.1.51:/root

数据库服务器操作

第四步：数据库服务器安装启动

[root@db01 ~]# yum install mariadb-server mariadb -y
[root@db01 ~]# systemctl restart mariadb.service 
[root@db01 ~]# systemctl enable mariadb.service 
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.

第五步:将推过来的数据进行解压

[root@db01 ~]# gzip -d all.sql.gz 
[root@db01 ~]# ll
total 688
-rw-r--r--  1 root root 699114 Jun 12 09:14 all.sql
-rw-------. 1 root root   1740 May 30 08:42 anaconda-ks.cfg

第六步：将数据导入到数据库服务器

[root@db01 ~]# mysql  <all.sql 

第七步：查询数据是否导入

[root@db01 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
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               |
| wordpress          |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> select user, host from mysql.user;
+-----------+------------+
| user      | host       |
+-----------+------------+
| root      | 127.0.0.1  |
| wordpress | 172.16.1.% |
| root      | ::1        |
|           | localhost  |
| root      | localhost  |
| wordpress | localhost  |
| root      | web01      |
+-----------+------------+
7 rows in set (0.00 sec)

MariaDB [(none)]> 

第八步：导入完成进行刷新（不刷新的话会有报错）

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

1)不刷新会报错

[root@db01 ~]# mysql -uwordpress -p123456 -h 172.16.1.51
Enter password: 
ERROR 1045 (28000): Access denied for user 'wordpress'@'db01' (using password: NO)

2)刷新完成

[root@db01 ~]# mysql -uwordpress -p123456 -h 172.16.1.51
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)]> Bye

网站服务器操作

第九步：修改配置文件

[root@web01 /usr/share/nginx/html/blog]# vim wp-config.php
// ** MySQL settings - You can get this info from your web host ** //
/** The name of the database for WordPress */
define( 'DB_NAME', 'wordpress' );

/** MySQL database username */
define( 'DB_USER', 'wordpress' );

/** MySQL database password */
define( 'DB_PASSWORD', '123456' );

/** MySQL hostname */
define( 'DB_HOST', '172.16.1.51' );(修改成数据库的ip地址)

/** Database Charset to use in creating database tables. */
define( 'DB_CHARSET', 'utf8mb4' );

[root@web01 /usr/share/nginx/html/blog]# grep -i  db_host wp-config.php 
define( 'DB_HOST', '172.16.1.51' );

第十步：关闭掉本地的数据库服务，然后连接远程的数据库服务器

[root@web01 /usr/share/nginx/html/blog]# systemctl stop mariadb.service  
[root@web01 /usr/share/nginx/html/blog]# mysql -uwordpress -p123456 -h172.16.1.51 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
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)]> Bye

第十一步：

在浏览器查看博客的数据时候丢失
至此，数据库迁移就完成了

https://www.processon.com/view/link/5d004e07e4b0cbb88a599f6a