mysqldump逻辑备份,将数据库的数据以逻辑的sql语句方式导出然而备份的方式有很多,根据不同的场景进行备份,物理备份可以cp,或者使用xtrabackup开源的备份工具mysqldump备份小于50G的数据,时间大概在4-6个小时不等,数据较大则使用其他方式即可通常备份在数据库丛库进行迁移,在进行增量的恢复即可全库备份
[root@NFS-WEB1 01]# mysqldump -uroot -ppassword -B -A --events -x|gzip >/tmp/back_$(date +%F).sql.gz
[root@NFS-WEB1 01]# ll /tmp/
total 552
-rw-r--r-- 1 root root 262651 Jan 14 08:18 back_2016-01-14.sql.gz
-B添加use数据库,如果没有则创建 -A备份所有库-x锁表,备份会影响读写gzip压缩
1,wordpress单库备份
[root@NFS-WEB1 01]# mysqldump -uroot -ppassword -B -x wordpress|gzip >/tmp/back_wordpress_$(date +%F).sql.gz
[root@NFS-WEB1 01]# ll /tmp/back_wordpress_2016-01-14.sql.gz
-rw-r--r-- 1 root root 122954 Jan 14 08:26 /tmp/back_wordpress_2016-01-14.sql.gz
[root@NFS-WEB1 01]#
2.10.0.0.80 mysql恢复:
[root@node opt]# gzip -d back_wordpress_2016-01-14.sql.gz
[root@node opt]# mysql -uroot -p < /opt/back_wordpress_2016-01-14.sql
Enter password:
[root@node opt]#
查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| wordpress |
+--------------------+
5 rows in set (0.02 sec)
mysql> use wordpress
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_wordpress |
+-----------------------+
| wp_commentmeta |
| wp_comments |
| wp_links |
| wp_options |
| wp_postmeta |
| wp_posts |
| wp_term_relationships |
| wp_term_taxonomy |
| wp_termmeta |
| wp_terms |
| wp_usermeta |
| wp_users |
+-----------------------+
12 rows in set (0.00 sec)
mysql>
查看当前用户
mysql> select user.host from mysql.user;
+-----------+
| host |
+-----------+
| 127.0.0.1 |
| ::1 |
| localhost |
| localhost |
| node |
| node |
+-----------+
6 rows in set (0.01 sec)
授权远程wordpress访问
mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON wordpress.* to 'blog'@'10.0.0.53' IDENTIFIED BY 'password.com';
Query OK, 0 rows affected (0.00 sec)
mysql> select user.host from mysql.user;
+-----------+
| host |
+-----------+
| 10.0.0.53 |
| 127.0.0.1 |
| ::1 |
| localhost |
| localhost |
| node |
| node |
+-----------+
7 rows in set (0.00 sec)
mysql>
3,修改远程wordpress的配置文件
vim /www/blog/wp-config.php
define('DB_HOST', '10.0.0.80');
###############这里的define('DB_HOST', '10.0.0.80');可修改###################
#1,可以使用域名:define('DB_HOST', 'www.linuxea123.com');
#2,在修改wordpress主机的/etc/hosts中文件,做解析10.0.0.80 www.linuxea123.com
#使用hosts可以轻松解决开发程序的连接,更加轻便
##############################################################################
/usr/local/nginx/sbin/nginx -s reload
而后即可看到测试页面