前言:
因为工作的原因,很多地方需要用到数据库查询,为了避免破坏真实存在的数据库,所以,我搭建了一个虚拟机,在搭建的虚拟机上将数据库进行了导入。这里,用了一个PHP myAdmin的工具,个人觉得还是非常方便控制管理数据库的。故做了下研究,写下此文章记录下。
PHP myAdmin简介
root@cent42:/var/www/html> yum install mysql-server
Loaded plugins: fastestmirror
Setting up Install Process
Loading mirror speeds from cached hostfile
Resolving Dependencies
--> Running transaction check
---> Package mysql-server.i686 0:5.1.73-5.el6_6 will be installed
--> Processing Dependency: mysql = 5.1.73-5.el6_6 for package: mysql-server-5.1.73-5.el6_6.i686
--> Processing Dependency: perl-DBI for package: mysql-server-5.1.73-5.el6_6.i686
--> Processing Dependency: perl-DBD-MySQL for package: mysql-server-5.1.73-5.el6_6.i686
--> Processing Dependency: perl(vars) for package: mysql-server-5.1.73-5.el6_6.i686
--> Processing Dependency: perl(strict) for package: mysql-server-5.1.73-5.el6_6.i686
--> Processing Dependency: perl(Sys::Hostname) for package: mysql-server-5.1.73-5.el6_6.i686
--> Processing Dependency: perl(POSIX) for package: mysql-server-5.1.73-5.el6_6.i686
--> Processing Dependency: perl(Getopt::Long) for package: mysql-server-5.1.73-5.el6_6.i686
--> Processing Dependency: perl(File::Temp) for package: mysql-server-5.1.73-5.el6_6.i686
--> Processing Dependency: perl(File::Path) for package: mysql-server-5.1.73-5.el6_6.i686
--> Processing Dependency: perl(File::Copy) for package: mysql-server-5.1.73-5.el6_6.i686
--> Processing Dependency: perl(File::Basename) for package: mysql-server-5.1.73-5.el6_6.i686
--> Processing Dependency: perl(Data::Dumper) for package: mysql-server-5.1.73-5.el6_6.i686
--> Processing Dependency: perl(DBI) for package: mysql-server-5.1.73-5.el6_6.i686
--> Processing Dependency: /usr/bin/perl for package: mysql-server-5.1.73-5.el6_6.i686
--> Running transaction check
---> Package mysql.i686 0:5.1.73-5.el6_6 will be installed
---> Package perl.i686 4:5.10.1-141.el6 will be installed
--> Processing Dependency: perl-libs = 4:5.10.1-141.el6 for package: 4:perl-5.10.1-141.el6.i686
--> Processing Dependency: perl-libs for package: 4:perl-5.10.1-141.el6.i686
--> Processing Dependency: perl(version) for package: 4:perl-5.10.1-141.el6.i686
--> Processing Dependency: perl(Pod::Simple) for package: 4:perl-5.10.1-141.el6.i686
--> Processing Dependency: perl(Module::Pluggable) for package: 4:perl-5.10.1-141.el6.i686
--> Processing Dependency: libperl.so for package: 4:perl-5.10.1-141.el6.i686
---> Package perl-DBD-MySQL.i686 0:4.013-3.el6 will be installed
---> Package perl-DBI.i686 0:1.609-4.el6 will be installed
--> Running transaction check
---> Package perl-Module-Pluggable.i686 1:3.90-141.el6 will be installed
---> Package perl-Pod-Simple.i686 1:3.13-141.el6 will be installed
--> Processing Dependency: perl(Pod::Escapes) >= 1.04 for package: 1:perl-Pod-Simple-3.13-141.el6.i686
---> Package perl-libs.i686 4:5.10.1-141.el6 will be installed
---> Package perl-version.i686 3:0.77-141.el6 will be installed
--> Running transaction check
---> Package perl-Pod-Escapes.i686 1:1.04-141.el6 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=================================================================================================================
Package Arch Version Repository Size
=================================================================================================================
Installing:
mysql-server i686 5.1.73-5.el6_6 c6-xiawei 8.8 M
Installing for dependencies:
mysql i686 5.1.73-5.el6_6 c6-xiawei 904 k
perl i686 4:5.10.1-141.el6 c6-xiawei 9.7 M
perl-DBD-MySQL i686 4.013-3.el6 c6-xiawei 134 k
perl-DBI i686 1.609-4.el6 c6-xiawei 705 k
perl-Module-Pluggable i686 1:3.90-141.el6 c6-xiawei 40 k
perl-Pod-Escapes i686 1:1.04-141.el6 c6-xiawei 33 k
perl-Pod-Simple i686 1:3.13-141.el6 c6-xiawei 213 k
perl-libs i686 4:5.10.1-141.el6 c6-xiawei 594 k
perl-version i686 3:0.77-141.el6 c6-xiawei 52 k
Transaction Summary
=================================================================================================================
Install 10 Package(s)
Total download size: 21 M
Installed size: 59 M
Is this ok [y/N]: y
Downloading Packages:
(1/10): mysql-5.1.73-5.el6_6.i686.rpm | 904 kB 00:00
(2/10): mysql-server-5.1.73-5.el6_6.i686.rpm | 8.8 MB 00:00
(3/10): perl-libs-5.10.1-141.el6.i686.rpm | 594 kB 00:00
(4/10): perl-DBD-MySQL-4.013-3.el6.i686.rpm | 134 kB 00:00
(5/10): perl-Pod-Escapes-1.04-141.el6.i686.rpm | 33 kB 00:00
(6/10): perl-Pod-Simple-3.13-141.el6.i686.rpm | 213 kB 00:00
(7/10): perl-DBI-1.609-4.el6.i686.rpm | 705 kB 00:00
(8/10): perl-Module-Pluggable-3.90-141.el6.i686.rpm | 40 kB 00:00
(9/10): perl-version-0.77-141.el6.i686.rpm | 52 kB 00:00
(10/10): perl-5.10.1-141.el6.i686.rpm | 9.7 MB 00:00
-----------------------------------------------------------------------------------------------------------------
Total 24 MB/s | 21 MB 00:00
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : 1:perl-Pod-Escapes-1.04-141.el6.i686 1/10
Installing : 1:perl-Pod-Simple-3.13-141.el6.i686 2/10
Installing : 1:perl-Module-Pluggable-3.90-141.el6.i686 3/10
Installing : 3:perl-version-0.77-141.el6.i686 4/10
Installing : 4:perl-libs-5.10.1-141.el6.i686 5/10
Installing : 4:perl-5.10.1-141.el6.i686 6/10
Installing : perl-DBI-1.609-4.el6.i686 7/10
Installing : perl-DBD-MySQL-4.013-3.el6.i686 8/10
Installing : mysql-5.1.73-5.el6_6.i686 9/10
Installing : mysql-server-5.1.73-5.el6_6.i686 10/10
Verifying : mysql-5.1.73-5.el6_6.i686 1/10
Verifying : mysql-server-5.1.73-5.el6_6.i686 2/10
Verifying : 4:perl-libs-5.10.1-141.el6.i686 3/10
Verifying : perl-DBD-MySQL-4.013-3.el6.i686 4/10
Verifying : 1:perl-Pod-Escapes-1.04-141.el6.i686 5/10
Verifying : 1:perl-Pod-Simple-3.13-141.el6.i686 6/10
Verifying : perl-DBI-1.609-4.el6.i686 7/10
Verifying : 1:perl-Module-Pluggable-3.90-141.el6.i686 8/10
Verifying : 3:perl-version-0.77-141.el6.i686 9/10
Verifying : 4:perl-5.10.1-141.el6.i686 10/10
Installed:
mysql-server.i686 0:5.1.73-5.el6_6
Dependency Installed:
mysql.i686 0:5.1.73-5.el6_6 perl.i686 4:5.10.1-141.el6
perl-DBD-MySQL.i686 0:4.013-3.el6 perl-DBI.i686 0:1.609-4.el6
perl-Module-Pluggable.i686 1:3.90-141.el6 perl-Pod-Escapes.i686 1:1.04-141.el6
perl-Pod-Simple.i686 1:3.13-141.el6 perl-libs.i686 4:5.10.1-141.el6
perl-version.i686 3:0.77-141.el6
Complete!
root@cent42:/var/www/html>
2)启动服务器
root@cent42:/root> service mysqld start
Initializing MySQL database: WARNING: The host 'cent42' could not be looked up with resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MySQL version. The MySQL daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MySQL privileges !
Installing MySQL system tables...
OK
Filling help tables...
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h cent42 password 'new-password'
Alternatively you can run:
/usr/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl
Please report any problems with the /usr/bin/mysqlbug script!
[ OK ]
Starting mysqld: [ OK ]
3)放通防火墙
使用命令如下:
iptables -A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT (关于iptabls的详细解释请见iptables章节)
4) 执行安全性设置
包括设置root密码、删除测试数据库等:这里设置root密码将是你登陆数据库使用的密码)
root@cent42:/root> /usr/bin/mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
Set root password? [Y/n] Y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
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...
... Success!
- 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!
root@cent42:/root>
在“/etc/sysconfig/iptables”文件中“-A INPUT ... --dport 22 -j ACCEPT”一行后面添加放通80端口的规则。具体命令如下:
"-A INPUT -p tcp -m state --state NEW -m tcp --dport 80 -j ACCEPT"
重启防火墙(service iptables restart)
上述操作完成后,你可以通过浏览器去访问如下的地址(地址就是你搭建虚拟机的地址)
http://135.251.124.24/
会出现一个Apache的界面
现在,你只要在服务器的“/var/www/html/”这个目录中添加相关的html文件,就可以访问了。
3.安装PHP
yum install php php-mysql
这里仅仅安装了PHP软件本身,和用于php-mysql连接的库,实际在访问PHP的时候,还需要装其他的插件,所以,如何找打和PHP相关的插件呢?可以使用如下命令:
yum search php-,这里命令可以列出所有的PHP的安装包,如果你想知道每个模块是干什么的,可以使用“yum info php-common”这样的命令去查看
安装完成后,需要重启Apache HTTP Server :
service httpd restart
测试下PHP是否安装成功,能否解析PHP代码。进入如下目录:
cd /var/www/html/
编辑如下文件:
root@cent42:/var/www/html> vi info.php
添加如下信息:
<?php
phpinfo();
?>
保存退出后,浏览器中输入:
http://135.251.124.24//info.php,应该就可以看到PHP的网页了。
安装phpMyAdmin
找到一个phpMyAdmin的包,将其放在你的服务器上,我是直接放在了/var/www/html下,然后解压,将名称更改为phpMyAdmin即可。这样,你就可以通过访问http://135.252.4.25/phpMyAdmin/来访问phpMyAdmin,从而来访问管理你的数据库了。
用户名就是root,密码就是你在上面设置的密码。
4.如何导入导出数据库
如果你有一个源数据库,你想将它导入到你搭建的服务器上,那么应该如何实现呢,可以用如下的命令:
先通过如下命令到源数据库中将数据导出:
mysqldump --password=xxx(密码) --user=root(用户) --add-drop-table --databases xxx(源DB的名字) --result-file=/home/xxx.sql(目标)
然后登陆到目标数据库(我这里就是登陆到24上),创建一个新的database:
mysqladmin --user=root --password=xxx create mh_db_bak_20160811
然后将刚才导出的文件上传到目标数据库的同一个服务器上(通过FTP等先将导出的数据库传入到你的目标服务器上),执行下面的命令导入文件:
mysql --user=root --password=xxx mh_db_bak_20160811 < cif4db.sql
这样,你打开你的PHPMyAdmin的时候,就可以看到导入的数据库了,可以执行你的查询等操作啦。