文章目录
1. 数据库的备份还原
1.1 备份数据库
1)首先建立一个自己的数据库:
MariaDB [(none)]> CREATE DATABASE song;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| song |
+--------------------+
4 rows in set (0.00 sec)
2)数据库中建表写入数据:
MariaDB [(none)]> USE song;
Database changed
MariaDB [song]> CREATE TABLE userlist(
-> username varchar(50) not null,
-> password varchar(50) not null
-> );
Query OK, 0 rows affected (0.00 sec)
MariaDB [song]> SHOW TABLES;
+----------------+
| Tables_in_song |
+----------------+
| userlist |
+----------------+
1 row in set (0.00 sec)
MariaDB [song]> INSERT INTO userlist VALUES ('Gao','123456');
Query OK, 1 row affected (0.00 sec)
MariaDB [song]> SELECT * FROM userlist;
+----------+----------+
| username | password |
+----------+----------+
| Gao | 123456 |
+----------+----------+
1 row in set (0.00 sec)
3)备份数据库中的song库:直接备份这种方式是将建库建表执行的命令保存下来,回复的时候,执行一遍。
[root@mariadb ~]# mysqldump -uroot -p123456 song > /mnt/song.sql
[root@mariadb ~]# cat /mnt/song.sql
-- Table structure for table `userlist`
--
DROP TABLE IF EXISTS `userlist`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `userlist` (
`username` varchar(50) NOT NULL,
`password` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `userlist`
--
LOCK TABLES `userlist` WRITE;
/*!40000 ALTER TABLE `userlist` DISABLE KEYS */;
INSERT INTO `userlist` VALUES ('Gao','123456');
/*!40000 ALTER TABLE `userlist` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
3)使用--no-data
参数,只保存表的结构,不保存表中数据。
[root@mariadb ~]# mysqldump -uroot -p123456 song --no-data > /mnt/song1.sql
[root@mariadb ~]# cat /mnt/song1.sql
-- Table structure for table `userlist`
--
DROP TABLE IF EXISTS `userlist`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `userlist` (
`username` varchar(50) NOT NULL,
`password` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
4)使用--all-database
完全备份所有的库;使用--all-database --no-data
备份所有的库的结构不要数据。
1.2 执行建库命令恢复数据库
1)删除song库:
[root@mariadb ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> DROP DATABASE song;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
2)恢复数据库包括数据库中的数据:无法直接恢复,文本中没有建立数据库这一项
[root@mariadb ~]# mysql -uroot -p123456 song < /mnt/song.sql
ERROR 1049 (42000): Unknown database 'song'
3)建立数据库,并恢复:
[root@mariadb ~]# mysql -uroot -p123456 -e "CREATE DATABASE song"
[root@mariadb ~]# mysql -uroot -p123456 song < /mnt/song.sql
4)查看库中数据是否恢复:(恢复成功)
[root@mariadb ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SELECT username FROM song.userlist;
+----------+
| username |
+----------+
| Gao |
+----------+
1 row in set (0.00 sec)
5)删库,恢复表结构不恢复数据:
MariaDB [(none)]> DROP DATABASE song;
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]> quit
Bye
[root@mariadb ~]# mysql -uroot -p123456 -e "CREATE DATABASE song"
[root@mariadb ~]# mysql -uroot -p123456 song < /mnt/song1.sql
6)查看数据是否存在:(数据不存在、库存在)
MariaDB [(none)]> SELECT username FROM song.userlist;
Empty set (0.00 sec)
MariaDB [(none)]> DESC song.userlist;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(50) | NO | | NULL | |
| password | varchar(50) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
1.3 修改备份文件恢复数据库
1)修改备份文件,添加建库,进入数据库的命令行:
[root@mariadb ~]# vim /mnt/song.sql
--
-- Table structure for table `userlist`
--
CREATE DATABASE song;
USE song;
2)直接通过文件恢复:(恢复成功)
[root@mariadb ~]# mysql -uroot -p123456 < /mnt/song.sql
[root@mariadb ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 18
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, 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 |
| song |
+--------------------+
4 rows in set (0.00 sec)
2.数据库的管理密码问题
2.1 已知root密码修改root密码
[root@mariadb ~]# mysqladmin -uroot -p123456 password song123
[root@mariadb ~]# mysql -uroot -psong123
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, 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 |
| song |
+--------------------+
4 rows in set (0.00 sec)
2.2 忘记root密码修改root密码
1)关闭数据库服务:
[root@mariadb ~]# systemctl stop mariadb.service
2)将安全检测打入后台:
[root@mariadb ~]# systemctl stop mariadb.service
[root@mariadb ~]# mysqld_safe --skip-grant-tables &
[1] 3371
[root@mariadb ~]# 200901 21:21:57 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
200901 21:21:57 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
3)再结合直接输入mysql进入数据库:
[root@mariadb ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
4)直接修改root用户密码:为加密的123456.
MariaDB [(none)]> UPDATE mysql.user SET Password=Password('123456') WHERE User='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 3 Changed: 1 Warnings: 0
5)杀死安全进程,及mysql所有的进程,重启mariadb服务:
[root@mariadb ~]# killall -9 mysqld_safe
[root@mariadb ~]# ps aux | grep mysql
root 3371 0.0 0.1 113252 1576 pts/0 S 21:21 0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
mysql 3514 0.0 8.5 841368 86640 pts/0 Sl 21:21 0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
root 3579 0.0 0.0 112648 960 pts/0 R+ 21:27 0:00 grep --color=auto mysql
[root@mariadb ~]# kill -9 3371 3514
[root@mariadb ~]# systemctl restart mariadb.service
6)不输密码进入数据库:失败
[root@mariadb ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
6)输入新修改的密码:
[root@mariadb ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
3. 安装图形管理界面
1)下载图形管理工具安装包。https://www.phpmyadmin.net/downloads/
[root@mariadb ~]# wget https://files.phpmyadmin.net/phpMyAdmin/3.4.0/phpMyAdmin-3.4.0-all-languages.zip
2)给安装包重命名:
[root@mariadb ~]# mv phpMyAdmin-3.4.0-all-languages/ phpMyAdmin
2)安装httpd服务:
[root@mariadb ~]# yum install httpd -y
Installed:
httpd.x86_64 0:2.4.6-45.el7
Dependency Installed:
apr.x86_64 0:1.4.8-3.el7 apr-util.x86_64 0:1.5.2-6.el7
httpd-tools.x86_64 0:2.4.6-45.el7 mailcap.noarch 0:2.1.41-2.el7
Complete!
2)将图形管理工具复制到httpd的默认发布页面:
[root@mariadb ~]# mv phpMyAdmin-3.4.0-all-languages/ phpMyAdmin
[root@mariadb ~]# mv phpMyAdmin /var/www/html/
[root@mariadb ~]# cd /var/www/html/
[root@mariadb html]# ls
phpMyAdmin
3)进入图形工具,修改配置:
[root@mariadb html]# cd phpMyAdmin/
[root@mariadb phpMyAdmin]# vim Documentation.txt
$cfg['blowfish_secret'] = 'ba17c1ec07d65003'; # 复制这一段
4)将PHP样本文件复制,复制刚才的一段:
[root@mariadb phpMyAdmin]# cp config.sample.inc.php config.inc.php
[root@mariadb phpMyAdmin]# vim config.inc.php
*/
$cfg['blowfish_secret'] = 'ba17c1ec07d65003'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
/*
* Servers configuration
5)安装php解析工具可mysql的php支持模块:
[root@mariadb ~]# yum install php php-mysql -y
Installed:
php.x86_64 0:5.4.16-42.el7 php-mysql.x86_64 0:5.4.16-42.el7
Dependency Installed:
libzip.x86_64 0:0.10.1-8.el7 php-cli.x86_64 0:5.4.16-42.el7
php-common.x86_64 0:5.4.16-42.el7 php-pdo.x86_64 0:5.4.16-42.el7
Complete!
[root@mariadb ~]# systemctl restart httpd
[root@mariadb ~]#
6)浏览器访问:
7)输入root用户和他的密码进入进行管理:
8)接着就可以建库,建表,添加数据都是数据页面。