备份MySQL数据库并恢复
备份的方法很多, 官方文档 http://dev.mysql.com/doc/
本文主要讲如何使用mysqldump备份并恢复MySQL. mysqldump是一个高效的备份MySQL数据库的工具.它创建一个*.sql文件,该文件带有原数据库的DROP table, CREATE table 和INSERT into sql-statements. 使用mysqldump,你可以用一条命令备份本地数据库并且同时恢复到远端服务器.
基本用法:
backup # mysqldump -u root -p[password] [database_name] > dumpfilename.sql
restore # mysqldump -u root -p[password] [database_name] < dumpfilename.sql
注意 -u 后面有空格 -p没有空格直接跟密码 restore数据库时 目标数据库必须存在 也就是先要创建一个数据库 才能做restore的动作
下面开始举例子:
1.备份单个数据库
# mysqldump -u root -p[password] store > store.sql
将数据库sotre备份至当前目录并且命名为store.sql. store.sql将包含store的drop table, create table and insert命令的所有tables.
2.备份多个数据库
如果你想备份多个数据库,首先请指定你想备份的数据库的名字
#mysql -u root -p[password] 进入数据库
mysql> show database;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bugs |
| mysql |
| store |
+--------------------+
4 rows in set (0.00 sec)
你想备份数据库store和bugs, 执行命令
#mysqldump -u root -p[password] --databases bugs store > bugs_store.sql
验证bugs_store.sql包含两个数据库
# grep -i "Current database:" /tmp/bus_store.sql
-- Current Database: `mysql`
-- Current Database: `store`
3.备份所有数据库
#mysqldump -u root -p[password] --all-databases > /tmp/all-databases.sql
4.备份指定table
备份数据库store中的accounts_contacts到目录/tmp/store_accounts_contacts.sql
# mysqldump -u root -p[password] store accounts_contacts \
> /tmp/store_accounts_contacts.sql
如何恢复数据库
1.恢复数据库store.sql到数据库store. 首先,数据库store必须存在,创建数据库store如下
#mysql -u root -p[password]
mysql> create database store;
mysql> quit;
恢复数据库命令
# mysql -u root -p[password] store < /tmp/store.sql
2.备份本地数据库的同时恢复数据库到远端服务器
备份本地数据库store 恢复store到远端服务器并且重命名为store1
请注意必须先在远端服务器创建数据库store1
# mysqldump -u root -p[password] store | mysql -u root -p[password] --host=remote-server -C store1
好了,问题来了:
备份多个数据库到一个文件bugs_store.sql后, 如何只恢复其中一个数据库store呢?
操作过程中出现的问题:
备份后 在远端恢复数据之后 打开网站 显示报错
Call to undefined function: mysqli_connect(). Please install the MySQL Connector for PHP
于是打开info.php页面, 发现Additional.ini files parsed一项比原来少了很多, 一项一项安装添加比较 始终还是报同样的错误
重新配置php5-mysql好了,执行命令
apt-get purge php5-mysql
apt-get autoremove
apt-get install php5-mysql
输出以下信息
Selecting previously unselected package php5-mysql.
(Reading database ... 331424 files and directories currently installed.)
Preparing to unpack .../php5-mysql_5.6.14+dfsg-0+deb8u1_amd64.deb ...
Unpacking php5-mysql (5.6.14+dfsg-0+deb8u1) ...
Processing triggers for php5-fpm (5.6.14+dfsg-0+deb8u1) ...
Setting up php5-mysql (5.6.14+dfsg-0+deb8u1) ...
Creating config file /etc/php5/mods-available/mysql.ini with new version
php5_invoke: Enable module mysql for fpm SAPI
php5_invoke: Enable module mysql for cli SAPI
Creating config file /etc/php5/mods-available/mysqli.ini with new version
php5_invoke: Enable module mysqli for fpm SAPI
php5_invoke: Enable module mysqli for cli SAPI
Creating config file /etc/php5/mods-available/pdo_mysql.ini with new version
php5_invoke: Enable module pdo_mysql for fpm SAPI
php5_invoke: Enable module pdo_mysql for cli SAPI
Processing triggers for php5-fpm (5.6.14+dfsg-0+deb8u1) ...
重新配置完毕, 打开info.php页面,发现好多配置瞬间有了. 再次访问server_ip 正常访问. 问题解决.
Saturday, March 19, 2016
LEMP配置-Debian Jessie
LEMP配置手册
Linux + Nginx + MySQL + PHP-FPM 搭建网站平台
1.更新系统
apt-get update
apt-get upgrade
2.安装nginx
apt-get install nginx
systemctl start nginx 开启nginx服务
systemctl enable nginx 设置开机启动nginx
可能出现的问题:
dpkg: error processing package nginx (--configure):
dependency problems - leaving unconfigured
Processing triggers for systemd (215-17+deb8u1) ...
Errors were encountered while processing:
nginx-full
nginx
E: Sub-process /usr/bin/dpkg returned an error code (1)
解决办法:
编辑 /etc/nginx/sites-available/default 注释掉行 listen [::]:80 default_server;
systemctl restart nginx
apt-get install nginx 目的是配置Nginx
此时访问 server_ip 出现welcom表示配置成功.
Welcome to nginx on Debian!
If you see this page, the nginx web server is successfully installed and working on Debian. Further configuration is required.
For online documentation and support please refer to nginx.org
Please use the reportbug tool to report bugs in the nginx package with Debian. However, check existing bug reports before reporting a new bug.
Thank you for using debian and nginx.
3.安装MySQL
apt-get install mysql-server
安装过程中请根据提示设置root密码
安装完成后, 请执行安全安装选项
mysql_secure_installation
输入刚才设置的SQL root密码, 当被问到是否修改root密码时, 请输入n.
过程如下:
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
You already have a root password set, so you can safely answer 'n'.
Change the root password? [Y/n] n
... skipping.
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] y
... Success!
By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y
- Dropping test database...
ERROR 1008 (HY000) at line 1: Can't drop database 'test'; database doesn't exist
... Failed! Not critical, keep moving...
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!
设置开机启动mysql systemctl enable mysql
安装过程可能会报错:
Job for mysql.service failed. See 'systemctl status mysql.service' and 'journalctl -xn' for details.
invoke-rc.d: initscript mysql, action "start" failed.
dpkg: error processing package mysql-server-5.5 (--configure):
subprocess installed post-installation script returned error exit status 1
dpkg: dependency problems prevent configuration of mysql-server:
mysql-server depends on mysql-server-5.5; however:
Package mysql-server-5.5 is not configured yet.
dpkg: error processing package mysql-server (--configure):
dependency problems - leaving unconfigured
Processing triggers for libc-bin (2.19-18+deb8u3) ...
Errors were encountered while processing:
mysql-server-5.5
mysql-server
E: Sub-process /usr/bin/dpkg returned an error code (1)
root@kali:/var/www/html# systemctl status mysql.service
● mysql.service - LSB: Start and stop the mysql database server daemon
Loaded: loaded (/etc/init.d/mysql)
Active: failed (Result: exit-code) since Fri 2016-03-18 16:53:04 CST; 30s ago
Process: 11831 ExecStart=/etc/init.d/mysql start (code=exited, status=127)
Mar 18 16:53:02 kali mysql[11831]: /etc/init.d/mysql: WARNING: /etc/mysql/my.cnf cannot be read. See README.Debian.gz ... (warning).
Mar 18 16:53:04 kali mysql[11831]: Starting MySQL database server: mysqld ..
Mar 18 16:53:04 kali mysql[11831]: /etc/init.d/mysql: line 121: /etc/mysql/debian-start: No such file or directory
Mar 18 16:53:04 kali systemd[1]: mysql.service: control process exited, code=exited status=127
Mar 18 16:53:04 kali systemd[1]: Failed to start LSB: Start and stop the mysql database server daemon.
Mar 18 16:53:04 kali systemd[1]: Unit mysql.service entered failed state.
解决办法:
apt-get purge mysql-server
apt-get autoremove
apt-get clean
apt-get autoclean
apt-get update
apt-get install mysql-server
4.安装PHP-FPM
apt-get install php5-fpm php5-mysql
下一步要做的就是修改nginx配置文件, 修改之前请先备份.
mv /etc/nginx/sites-available/default /etc/nginx/sites-available/default.old
vim /etc/nginx/sites-available/default
基本配置如下:
server {
listen 80;
server_name your_website_name.com;
root /var/www/html;
index index.php index.html index.htm index.nginx-debian.html;
location / {
try_files $uri $uri/ =404;
}
error_page 404 /404.html;
error_page 500 502 503 504 /50x.html;
location = /50x.html {
root /var/www/html;
}
location ~ \.php$ {
try_files $uri =404;
fastcgi_pass unix:/var/run/php5-fpm.sock;
fastcgi_index index.php;
fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
include fastcgi_params;
}
}
保存退出.
5.测试
创建一个文件名为info.php的文件 放在目录/var/www/html下
vim /var/www/html/info.php
文件内容如下:
<?php
phpinfo();
?>
重启nginx服务以验证.
systemctl restart nginx
访问页面server_ip/info.php 你将会看到PHP版本, 包含的modules 等等.
LEMP配置完成!
Thanks!
Linux + Nginx + MySQL + PHP-FPM 搭建网站平台
1.更新系统
apt-get update
apt-get upgrade
2.安装nginx
apt-get install nginx
systemctl start nginx 开启nginx服务
systemctl enable nginx 设置开机启动nginx
可能出现的问题:
dpkg: error processing package nginx (--configure):
dependency problems - leaving unconfigured
Processing triggers for systemd (215-17+deb8u1) ...
Errors were encountered while processing:
nginx-full
nginx
E: Sub-process /usr/bin/dpkg returned an error code (1)
解决办法:
编辑 /etc/nginx/sites-available/default 注释掉行 listen [::]:80 default_server;
systemctl restart nginx
apt-get install nginx 目的是配置Nginx
此时访问 server_ip 出现welcom表示配置成功.
Welcome to nginx on Debian!
If you see this page, the nginx web server is successfully installed and working on Debian. Further configuration is required.
For online documentation and support please refer to nginx.org
Please use the reportbug tool to report bugs in the nginx package with Debian. However, check existing bug reports before reporting a new bug.
Thank you for using debian and nginx.
3.安装MySQL
apt-get install mysql-server
安装过程中请根据提示设置root密码
安装完成后, 请执行安全安装选项
mysql_secure_installation
输入刚才设置的SQL root密码, 当被问到是否修改root密码时, 请输入n.
过程如下:
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
You already have a root password set, so you can safely answer 'n'.
Change the root password? [Y/n] n
... skipping.
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] y
... Success!
By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y
- Dropping test database...
ERROR 1008 (HY000) at line 1: Can't drop database 'test'; database doesn't exist
... Failed! Not critical, keep moving...
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!
设置开机启动mysql systemctl enable mysql
安装过程可能会报错:
Job for mysql.service failed. See 'systemctl status mysql.service' and 'journalctl -xn' for details.
invoke-rc.d: initscript mysql, action "start" failed.
dpkg: error processing package mysql-server-5.5 (--configure):
subprocess installed post-installation script returned error exit status 1
dpkg: dependency problems prevent configuration of mysql-server:
mysql-server depends on mysql-server-5.5; however:
Package mysql-server-5.5 is not configured yet.
dpkg: error processing package mysql-server (--configure):
dependency problems - leaving unconfigured
Processing triggers for libc-bin (2.19-18+deb8u3) ...
Errors were encountered while processing:
mysql-server-5.5
mysql-server
E: Sub-process /usr/bin/dpkg returned an error code (1)
root@kali:/var/www/html# systemctl status mysql.service
● mysql.service - LSB: Start and stop the mysql database server daemon
Loaded: loaded (/etc/init.d/mysql)
Active: failed (Result: exit-code) since Fri 2016-03-18 16:53:04 CST; 30s ago
Process: 11831 ExecStart=/etc/init.d/mysql start (code=exited, status=127)
Mar 18 16:53:02 kali mysql[11831]: /etc/init.d/mysql: WARNING: /etc/mysql/my.cnf cannot be read. See README.Debian.gz ... (warning).
Mar 18 16:53:04 kali mysql[11831]: Starting MySQL database server: mysqld ..
Mar 18 16:53:04 kali mysql[11831]: /etc/init.d/mysql: line 121: /etc/mysql/debian-start: No such file or directory
Mar 18 16:53:04 kali systemd[1]: mysql.service: control process exited, code=exited status=127
Mar 18 16:53:04 kali systemd[1]: Failed to start LSB: Start and stop the mysql database server daemon.
Mar 18 16:53:04 kali systemd[1]: Unit mysql.service entered failed state.
解决办法:
apt-get purge mysql-server
apt-get autoremove
apt-get clean
apt-get autoclean
apt-get update
apt-get install mysql-server
4.安装PHP-FPM
apt-get install php5-fpm php5-mysql
下一步要做的就是修改nginx配置文件, 修改之前请先备份.
mv /etc/nginx/sites-available/default /etc/nginx/sites-available/default.old
vim /etc/nginx/sites-available/default
基本配置如下:
server {
listen 80;
server_name your_website_name.com;
root /var/www/html;
index index.php index.html index.htm index.nginx-debian.html;
location / {
try_files $uri $uri/ =404;
}
error_page 404 /404.html;
error_page 500 502 503 504 /50x.html;
location = /50x.html {
root /var/www/html;
}
location ~ \.php$ {
try_files $uri =404;
fastcgi_pass unix:/var/run/php5-fpm.sock;
fastcgi_index index.php;
fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
include fastcgi_params;
}
}
保存退出.
5.测试
创建一个文件名为info.php的文件 放在目录/var/www/html下
vim /var/www/html/info.php
文件内容如下:
<?php
phpinfo();
?>
重启nginx服务以验证.
systemctl restart nginx
访问页面server_ip/info.php 你将会看到PHP版本, 包含的modules 等等.
LEMP配置完成!
Thanks!
Subscribe to:
Posts (Atom)