LNMP环境中的数据库迁移为独立的服务器_php教程

上一篇: php中函数的类型提示和文件读取功... 下一篇:PHP Mysql support: 是mysql 还是...

LNMP环境中的数据库迁移为独立的服务器

发布时间：2022-04-30 发布网站：脚本宝典

脚本宝典收集整理的这篇文章主要介绍了LNMP环境中的数据库迁移为独立的服务器，脚本宝典觉得挺不错的，现在分享给大家，也给大家做个参考。

环境： centos 6.5 ip:192.168.0.118 Nginx、PHP、MysqL

centos 6.5 ip:192.168.0.117 MysqL

现在我们需要把数据库迁移到192.168.0.117机器上：

首先我们需要在118机器上备份数据库：

MysqLdump 最常用的备份工具：

逻辑备份：小于50G的数据量，4-6个小时ok

原理：将数据库的数据以逻辑的sql语句的方式导出

物理备份：

scp /application/MysqL 拷贝到独立数据库上就可以
xtrabackup 开源的物理备份工具

下面我们以常用的逻辑来备份：

-A 备份所有库
-B 备份多个库，并添加use 库名：create database 库等的功能
-X 锁表，备份会影响读写，尽量晚上执行。
|gzip 压缩效率
.sql.gz 表示sql语句数据，.gz是压缩包。

MysqL> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bqh                |
| jyw                |
| MysqL              |
| PErformance_schema |
| test               |
| wordpress          |
+--------------------+
7 rows in set (0.06 sec)

MysqL> quIT
[[email protected] MysqL]# MysqLdump -uroot -p123456 -A -B -X|gzip>/opt/bak_$(date +%F).sql.gz
-- Warning: Skipping the data of table MysqL.event. Specify the --events option explicitly.
[[email protected] MysqL]# ll /opt/
总用量 348
-rw-r--r-- 1 root root 354461 6月  30 12:56 bak_2019-06-30.sql.gz
[[email protected] MysqL]# rm -rf /opt/bak_2019-06-30.sql.gz 
[[email protected] MysqL]# MysqLdump -uroot -p123456 -A -B -X --events|gzip>/opt/bak_$(date +%F).sql.gz  #备份全库
[[email protected] MysqL]# ll /opt/
总用量 348
-rw-r--r-- 1 root root 354463 6月  30 13:00 bak_2019-06-30.sql.gz
[[email protected] MysqL]# MysqLdump -uroot -p123456  -B -X wordpress|gzip>/opt/bak_wordpress.sql.gz #只备份指定的库
[[email protected] MysqL]# ll /opt/
总用量 544
-rw-r--r-- 1 root root 354463 6月  30 13:00 bak_2019-06-30.sql.gz
-rw-r--r-- 1 root root 199211 6月  30 13:01 bak_wordpress.sql.gz

注：Warning: Skipping the data of table MysqL.event. Specify the --events option explicitly.

解决方法：MysqLdump备份时加参数 --events

将备份的库scp到117机器上去：

[[email protected] MysqL]# scp -rp -P22 /opt/bak_wordpress.sql.gz [email protected]:/opt/
[email protected]‘s password: 
bak_wordpress.sql.gz                                              100%  195KB 194.5KB/s   00:00    
[[email protected] MysqL]#

我们上117机器/opt/下查看是否推送过来了：

现在我们在117机器上恢复数据：

[[email protected] opt]# ll
总用量 188
-rw-r--r--  1 root root 185857 6月  30 23:38 bak_wordpress.sql.gz
drwxr-xr-x. 2 root root   4096 11月 22 2013 rh
[[email protected] opt]# gunzip bak_wordpress.sql.gz 
[[email protected] opt]# ll
总用量 756
-rw-r--r--  1 root root 766279 6月  30 23:38 bak_wordpress.sql
drwxr-xr-x. 2 root root   4096 11月 22 2013 rh
[[email protected] opt]# less bak_wordpress.sql 
[[email protected] opt]# MysqL -uroot -p123456 <bak_wordpress.sql

我们进入数据库查看是否导入了：

[[email protected] opt]# MysqL -uroot -p123456
Welcome to the MysqL monitor.  Commands end with ; or \g.
Your MysqL connection id is 18
Server version: 5.5.32 MysqL CommUnity Server (GPL)

Copyright (c) 2000,2013,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 |
| test               |
| wordpress          |
+--------------------+
5 rows in set (0.00 sec)

MysqL> use wordpress;
Database changed
MysqL> show tables;
+-----------------------+
| Tables_in_wordpress   |
+-----------------------+
| bh_commentMeta        |
| bh_comments           |
| bh_links              |
| bh_options            |
| bh_postMeta           |
| bh_posts              |
| bh_term_relationships |
| bh_term_taxonomy      |
| bh_terms              |
| bh_userMeta           |
| bh_users              |
+-----------------------+
11 rows in set (0.00 sec)

MysqL>

我们现在打开web博客试试：

原因是我们没做授权和配置wp-config.php，现在我们要做117机器上给予授权：对于web来讲，数据授权：增删改查即可。

MysqL> select user,host From MysqL.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
|      | bqh-117   |
| root | bqh-117   |
|      | localhost |
| root | localhost |
+------+-----------+
6 rows in set (0.01 sec)

MysqL> grant select,insert,update,delete on wordpress.* to [email protected]‘192.168.0.%‘ identified by ‘123456‘;
Query OK,0 rows affected (0.02 sec)

MysqL> flush PRivileges;
Query OK,0 rows affected (0.00 sec)

MysqL> select user,host from MysqL.user;
+-----------+-------------+
| user      | host        |
+-----------+-------------+
| root      | 127.0.0.1   |
| wordpress | 192.168.0.% |
| root      | ::1         |
|           | bqh-117     |
| root      | bqh-117     |
|           | localhost   |
| root      | localhost   |
+-----------+-------------+
7 rows in set (0.00 sec)

MysqL>