After you create an account log in WordPress, you may want to change your WordPress username, as appropriate or due to security reason. However, you can not do this from the WordPress administration console. Continue reading “How to Change your WordPress Username, Nickname and Display Name in MySQL”
How to Install MySQL on CentOS 7 / RHEL 7 / Oracle Linux 7
In this quick guide, i will show you how to install MYSQL on CentOS 7 / RHEL 7 / Oracle Linux 7 instead of MariaDB.
MariaDB is the default implementation of MySQL in Red Hat Enterprise Linux 7 (RHEL 7) or CentOS 7.
MariaDB is a community-developed fork of the MySQL database project, and provides a replacement for MySQL.
However, in some cases, you still need to install MySQL as your deployment database on you linux server.
How to Install MySQL on CentOS 7 / RHEL 7 / Oracle Linux 7
1. Remove MariaDB installation :
If you server already have MariaDB database server installed, i would suggest you remove it first to avoid conflict.
# sudo yum remove mariadb-server -y
2. Download MySQL 5.7 repo file :
# wget https://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
3. Install MySQL 5.7 repo file :
# sudo rpm -ivh mysql57-community-release-el7-8.noarch.rpm
4. Install MySQL 5.7 database server :
# sudo yum install mysql-server -y
5. How to Start MySQL server in linux :
# sudo systemctl start mysqld
6. Enable auto start at boot :
# sudo systemctl enable mysqld
7. At the initial start up of the MySQL database server, the following happens, given that the data directory of the server is empty:
a) The server is initialized.
b) An SSL certificate and key files are generated in the data directory.
c) The validate_password plugin is installed and enabled.
d) A superuser account ‘root’@’localhost is created. The initial root password created can be found in the error log file. You can get the password by issue the following command :
# sudo grep 'temporary password' /var/log/mysqld.log 2016-06-19T23:08:09.439963Z 1 [Note] A temporary password is generated for root@localhost: sj-mMM;o%6Ll
8. Harden MySQL Server
Run the mysql_secure_installation script to address several security concerns in a default MySQL installation.
You will be given the choice to change the MySQL root password, remove anonymous user accounts, disable root logins outside of localhost, and remove test databases. It is recommended that you answer yes to these options.
# sudo mysql_secure_installation Securing the MySQL server deployment. Enter password for user root: The 'validate_password' plugin is installed on the server. The subsequent steps will run with the existing configuration of the plugin. Using existing password for root. Estimated strength of the password: 50 Change the password for root ? ((Press y|Y for Yes, any other key for No) : yes New password: Re-enter new password: Estimated strength of the password: 50 Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y 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? (Press y|Y for Yes, any other key for No) : 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? (Press y|Y for Yes, any other key for No) : no ... skipping. 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? (Press y|Y for Yes, any other key for No) : 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? (Press y|Y for Yes, any other key for No) : y Success. All done!
Resource : http://dev.mysql.com/
How to Enable and Grant Remote Access to MySQL Database Server
For reasons of security, remote access to MySQL database server is disabled by default because they are considered potential security threats. However, due to some reason, it is necessary to allow access from a remote location or web server. Let assume that we are making connection from remote web server IP called 192.168.0.3 for database called db1 for user user1 at remote MySQL server, 192.168.0.2, then we need to grant access to this IP address.
If the remote access is not enable you will get this error :
ERROR 1130 (HY000): Host ‘192.168.0.3’ is not allowed to connect to this MySQL server
IP Adress 1 : 192.168.0.2 – MySQL Server
IP Adress 2 : 192.168.0.3 – Web Server (Nginx or Apache)
1. Edit the my.cnf file :
# vim /etc/mysql/my.cnf
Comment out or remove below line :
#bind-address = 127.0.0.1
2. The following command will allow access to the MySQL database(192.168.0.2) from a remote web server IP address(192.168.0.3):
mysql> create user 'user1'@'192.168.0.3' identified by 'PASSWORD'; mysql> grant all on db1.* to 'user1'@'192.168.0.3';
3. Test the connection from the remote web server :
# mysql -u user1 -pPASSWORD -h 192.168.0.2
4. Verify the user privileges for user1 :
mysql> select * from information_schema.user_privileges where grantee like "'user1'%";
5. In case you want to revoke all options the access from all machine or web server(192.168.0.3) only :
mysql> revoke all privileges, grant option from 'user1'@'%';
mysql> revoke all privileges, grant option from 'user1'@'192.168.0.3';
How to Calculate and List Sizes of MySQL / MariaDB Databases
I am finding the best way to calculate and list sizes of MySQL or MariaDB Databases ?
In order to achive this, please do the following.
1. Enter to mysql root console:
# mysql -u root -p
2. Run the following SQL select query to calculate and list the sizes of all available databases in MySQL or MariaDB.
MariaDB [(none)]> SELECT table_schema AS "DB Name", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;
Output :
+--------------------+-------------+ | DB Name | Size (MB) | +--------------------+-------------+ | wordpressdb | 36.79408455 | | information_schema | 0.14062500 | | mysql | 0.62723351 | | oscommercedb | 1.42187500 | | performance_schema | 0.00000000 | +--------------------+-------------+ 5 rows in set (0.02 sec) MariaDB [(none)]>
How to Change the WordPress URLs in MySQL Database
Before this I have experienced issues in wordpress migration of servers moving from the test server with an unregistered domain URL (www.ehowstuff.local) to the new virtual private server (VPS) with a registered domain (www.ehowstuff.com). After struggling to do research on google, I found the steps below that save a lot of time.
Select the WordPress database :
mysql> use wordpressdb; 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
Check current value for ‘siteurl’ and ‘home’ :
mysql> SELECT option_name, option_value FROM wp_options WHERE option_name IN ('siteurl', 'home');
+-------------+----------------------------+
| option_name | option_value |
+-------------+----------------------------+
| home | http://www.ehowstuff.local |
| siteurl | http://www.ehowstuff.local |
+-------------+----------------------------+
2 rows in set (0.00 sec)
Update the value for ‘siteurl’ and ‘home’ :
mysql> UPDATE wp_options SET option_value = 'https://webhostinggeeks.com/howto' WHERE option_name IN ('siteurl', 'home');
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
Check the updated value for ‘siteurl’ and ‘home’ :
mysql> SELECT option_name, option_value FROM wp_options WHERE option_name IN ('siteurl', 'home');
+-------------+--------------------------+
| option_name | option_value |
+-------------+--------------------------+
| home | https://webhostinggeeks.com/howto |
| siteurl | https://webhostinggeeks.com/howto |
+-------------+--------------------------+
2 rows in set (0.00 sec)
Optionally you can use below query to achieve step 1:
mysql> UPDATE wp_options SET option_value = replace(option_value, 'http://www.ehowstuff.local', 'https://webhostinggeeks.com/howto') WHERE option_name = 'home' OR option_name = 'siteurl';
mysql> UPDATE wp_posts SET guid = replace(guid, 'http://www.ehowstuff.local','https://webhostinggeeks.com/howto');
mysql> UPDATE wp_posts SET post_content = replace(post_content, 'http://www.ehowstuff.local', 'https://webhostinggeeks.com/howto');
mysql> UPDATE wp_postmeta SET meta_value = replace(meta_value, 'http://www.ehowstuff.local', 'https://webhostinggeeks.com/howto');
How to Secure MySQL Server on CentOS 6.5 / CentOS 6.6
MySQL is the world’s most popular open source database and its the world’s second most widely used open-source relational database management system (RDBMS). MySQL default installation is not securely configured. For the sake of security, we need to run mysql_secure_installation wizard manually in order to perform basic MYSQL hardening on Virtual private server (VPS). The following steps has been tested on MySQL Community Server 5.5.39 that was running on CentOS 6.5 and CentOS 6.6.
1. Run mysql_secure_installation wizard :
[root@vps ]# 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!
2. Set “bind-address” parameter within the “[mysqld]” section in /etc/my.conf. Configure this to your VPS local loopback network device, which is “127.0.0.1”. please make sure that you only perform this step if you confirm no other server will need to access the database on your VPS.
[root@vps ~]# vi /etc/my.cnf
[mysqld] .. bind-address = 127.0.0.1 ..
3. Restart your mysqld server :
[root@vps ~]# service mysqld restart
4. Verify the mysqld port listen to 127.0.0.1 only :
[root@vps ~]# netstat -plunt | grep 3306 tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 8224/mysqld
How to Install Lighttpd With PHP5 (PHP-FPM) and MySQL on CentOS 6.5
Lighttpd (pronounced “lighty”) is an open-source web server as an alternative to Apache and Nginx. It is a secure, flexible, fast and designed for speed-critical environments. It has a low memory footprint and can handle large number of connections in one server especially for busier sites.
PHP is an acronym for “PHP Hypertext Preprocessor”; PHP is a widely-used server-side scripting language executed on the server.
PHP-FPM (FastCGI Process Manager) is an alternative PHP FastCGI implementation with some additional features useful for sites of any size, especially busier sites.
MySQL is a popular database solution for use in web applications.
This post will show you how to install Lighttpd With PHP5 (PHP-FPM) and MySQL on CentOS 6.5 VPS or dedicated server.
1. EPEL repository is another extra repository that creates, maintains, and manages a high quality set of additional packages for Enterprise Linux, including, but not limited to, Red Hat Enterprise Linux (RHEL) and CentOS server. How to Configure EPEL Repository on CentOS.
2. Install Lighttpd, MySQL and PHP5 work in Lighttpd through PHP-FPM :
[root@centos6-05 ~]# yum install lighttpd php php-fpm lighttpd-fastcgi php-mysql mysql mysql-server -y
3. Configure Lighttpd :
[root@centos6-05 ~]# vi /etc/lighttpd/lighttpd.conf
server.use-ipv6 = "enable"
Change to :
server.use-ipv6 = "disable"
4. Make lighttpd start at boot and also start lighttpd service:
[root@centos6-05 ~]# chkconfig --levels 235 lighttpd on
[root@centos6-05 ~]# /etc/init.d/lighttpd start
Browse your web server and Lighttpd welcome page should be displayed :
5. Configure PHP to work in Lighttpd through PHP-FPM :
[root@centos6-05 ~]# vi /etc/php-fpm.d/www.conf
Enable PHP-FPM use a TCP connection instead of unix socket :
; '/path/to/unix/socket' - to listen on a unix socket. ; Note: This value is mandatory. listen = 127.0.0.1:9000
Configure user and group to lighttpd :
.. .. ; RPM: apache Choosed to be able to access some dir as httpd user = lighttpd ; RPM: Keep a group allowed to write in log dir. group = lighttpd .. ..
6. Make php-fpm start at boot and also start the php-fpm service :
[root@centos6-05 ~]# chkconfig --levels 235 php-fpm on
[root@centos6-05 ~]# /etc/init.d/php-fpm start
7. Open and modify /etc/php.ini :
[root@centos6-05 ~]# vi /etc/php.ini
Uncomment the line cgi.fix_pathinfo=1
.. ; cgi.fix_pathinfo provides *real* PATH_INFO/PATH_TRANSLATED support for CGI. PHP's ; previous behaviour was to set PATH_TRANSLATED to SCRIPT_FILENAME, and to not grok ; what PATH_INFO is. For more information on PATH_INFO, see the cgi specs. Setting ; this to 1 will cause PHP CGI to fix its paths to conform to the spec. A setting ; of zero causes PHP to behave as before. Default is 1. You should fix your scripts ; to use SCRIPT_FILENAME rather than PATH_TRANSLATED. ; http://www.php.net/manual/en/ini.core.php#ini.cgi.fix-pathinfo cgi.fix_pathinfo=1 ..
8. Open and modify /etc/lighttpd/modules.conf :
[root@centos6-05 ~]# vi /etc/lighttpd/modules.conf
Uncomment the line include “conf.d/fastcgi.conf”:
.. ## FastCGI (mod_fastcgi) ## include "conf.d/fastcgi.conf" ..
9. Open and modify /etc/lighttpd/conf.d/fastcgi.conf :
Add below fastcgi.server at bottom of the file :
..
fastcgi.server += ( ".php" =>
((
"host" => "127.0.0.1",
"port" => "9000",
"broken-scriptfilename" => "enable"
))
)
..
10. Reload the PHP-FPM and Lighttpd service :
[root@centos6-05 ~]# /etc/init.d/php-fpm reload
[root@centos6-05 ~]# /etc/init.d/lighttpd reload
11. Since MySQl has been install, dont forget to make MySQL start at boot and start the MySQL service :
[root@centos6-05 ~]# chkconfig --levels 235 mysqld on
[root@centos6-05 ~]# /etc/init.d/mysqld start
12. Create info.php under ligghttpd document root :
[root@centos6-05 ~]# vi /var/www/lighttpd/info.php
<?php phpinfo(); ?>
Browse your page http://IP-Adress/info.php. Thats all.
How to Install and Configure Apache2, PHP and MySQL 5.6 on Ubuntu 14.04
LAMP stack is a group of open source software that installed together to let you run a server to host dynamic websites. “L” stand for Linux, “A” stand for Apache (to host Web server), “M” stand for MySQL(to store database) and “P” stand for PHP(to process dynamic content). With the release of Ubuntu 14.04 on April 17 2014, i would share the steps to setup Apache2, PHP and MySQL on Ubuntu 14.04 in order to run a dynamic websites. This may useful for those who plan to run their websites on Virtual private server (VPS) or dedicated server.
1. Install Apache2, MySQL and PHP :
ehowstuff@ubuntu14:~$ sudo apt-get install apache2 php5 php5-cgi libapache2-mod-php5 php5-common php-pear mysql-server-5.6 -y
During this installation, you will require to set MySQL’s root password :
2. Backup the original Apache2 configuration file :
ehowstuff@ubuntu14:~$ sudo cp -p /etc/apache2/conf-enabled/security.conf /etc/apache2/conf-enabled/security.conf.bak
3. Open security.conf and modify the OS to become Prod. For security reason, Prod will tells apache to only return Apache in the Server header, returned on every page request.
ehowstuff@ubuntu14:~$ sudo vi /etc/apache2/conf-enabled/security.conf
.. .. ServerTokens Prod .. .. ServerSignature Off .. ..
4. Add file extension that can be access :
ehowstuff@ubuntu14:~$ sudo vi /etc/apache2/mods-enabled/dir.conf
<IfModule mod_dir.c>
DirectoryIndex index.html index.cgi index.pl index.php index.xhtml index.htm
</IfModule>
5. Specify server name :
ehowstuff@ubuntu14:~$ sudo vi /etc/apache2/apache2.conf
# Do NOT add a slash at the end of the directory path. # #ServerRoot "/etc/apache2" ServerName ubuntu14.ehowstuff.local # # The accept se
6. Specify webmaster’s email :
ehowstuff@ubuntu14:~$ sudo vi /etc/apache2/sites-enabled/000-default.conf
ServerAdmin webmaster@ubuntu14.ehowstuff.local
DocumentRoot /var/www/html
7. Restart web server apache2 :
ehowstuff@ubuntu14:~$ sudo /etc/init.d/apache2 restart * Restarting web server apache2 [ OK ]
8. Near line 220: add extension for PHP :
ehowstuff@ubuntu14:~$ sudo vi /etc/apache2/mods-enabled/mime.conf
.. .. AddHandler php5-script .php .. ..
9. Comment and add your timezone :
ehowstuff@ubuntu14:~$ sudo vi /etc/php5/apache2/php.ini
.. .. date.timezone = "Asia/Kuala Lumpur" .. ..
After change php.ini, restart the apache :
ehowstuff@ubuntu14:~$ sudo /etc/init.d/apache2 restart * Restarting web server apache2 [ OK ]
10. Connect to MySQL :
ehowstuff@ubuntu14:~$ sudo mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 36 Server version: 5.6.17-0ubuntu0.14.04.1 (Ubuntu) Copyright (c) 2000, 2014, 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>
11. Show user info :
mysql> select user,host,password from mysql.user; +------------------+-----------+-------------------------------------------+ | user | host | password | +------------------+-----------+-------------------------------------------+ | root | localhost | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | | root | ubuntu14 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | | root | 127.0.0.1 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | | root | ::1 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | | debian-sys-maint | localhost | *9C063813F4CC3C2E09995B0D043C7375C5E5538A | +------------------+-----------+-------------------------------------------+ 5 rows in set (0.00 sec)
12. Show databases :
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.01 sec)
Done!!
How to Display processlist in MySQL
MySQL databases are great database in internet. It’s commonly used in WordPress and Drupal blog. When you are monitoring the performance of a WordPress or Drupal blog, do not forget to monitor the MySQL queries using existing tools including mytop, mtop and also running a SHOW PROCESSLIST from the mysql client command line. It will show all the queries are running and how long they take. The command SHOW PROCESSLIST actually shows you which threads are running in realtime. You can also can get this information from the mysqladmin processlist command and from the INFORMATION_SCHEMA PROCESSLIST table. If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads. However, if you really need to investigate or monitor overall website performance issue, please also look at other aspects of the system such memory and swapping as well as CPU utilization.
Here is an example of SHOW PROCESSLIST output:
show processlist;
mysql> show processlist; +----+---------------+-------------------+-------------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+---------------+-------------------+-------------+---------+------+-------+------------------+ | 23 | root | localhost | NULL | Query | 0 | NULL | show processlist | | 46 | wordpressuser | 192.168.0.5:38876 | wordpressdb | Sleep | 69 | | NULL | | 51 | root | localhost | wordpressdb | Sleep | 34 | | NULL | +----+---------------+-------------------+-------------+---------+------+-------+------------------+ 3 rows in set (0.00 sec)
Alternately you can run as below :
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 23
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: SHOW PROCESSLIST
*************************** 2. row ***************************
Id: 46
User: wordpressuser
Host: 192.168.0.5:38876
db: wordpressdb
Command: Sleep
Time: 73
State:
Info: NULL
*************************** 3. row ***************************
Id: 51
User: root
Host: localhost
db: wordpressdb
Command: Sleep
Time: 38
State:
Info: NULL
3 rows in set (0.00 sec)You have an option to run processlist in mysqladmin command. Below example show processlist every two second :
mysqladmin -u root -p -i 2 processlist
Example :
[root@mysql-server ~]# mysqladmin -u root -p -i 2 processlist Enter password: +----+------+-----------+----+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----+---------+------+-------+------------------+ | 6 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+----+---------+------+-------+------------------+ +----+------+-----------+----+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----+---------+------+-------+------------------+ | 6 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+----+---------+------+-------+------------------+ +----+------+-----------+----+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----+---------+------+-------+------------------+ | 6 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+----+---------+------+-------+------------------+ +----+------+-----------+----+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----+---------+------+-------+------------------+ | 6 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+----+---------+------+-------+------------------+
How to Secure your MySQL On VPS or Dedicated Server
Running a WordPress on a Virtual private Server or dedicated server is not an easy as running a WordPress on shared hosting server. There are a few things need to install and configure. Basically you will need web server(Apache, Nginx or Lighttpd) and database server(MySQL). The most popular database for WordPress platform is MySQL. Installation of the MySQL is very easy, but most of the webmaster will facing difficulties on the configuration part. Therefore i have prepared the article that will cover configuring and securing your MySQL on Virtual private Server(VPS) or on dedicated server. MySQL database is actually the brain of your website or blog. It will store all the configuration information, the posts, comments, login information, user information and etc. This article assumed that you already installed the MySQL server on your VPS or dedicated server and then you may proceed to configure and harden it as below :
1. Run pre-install mysql script, mysql_secure_installation. This will do the following :
a) Set the root password ensures that nobody can log into the MySQL root user without the proper authorization.
b) Remove anonymous users
c) Remove test database and access to it
d) Normally, root should only be allowed to connect from ‘localhost’. Disallow root login remotely if you want. However i prefer to disallow it later
[root@mysql-server ~]# /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] n
... skipping.
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!
2. List of MySQL users, make sure all users have password :
mysql> SELECT User,Host,Password FROM mysql.user; +---------------+-------------+-------------------------------------------+ | User | Host | Password | +---------------+-------------+-------------------------------------------+ | root | localhost | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | | root | mysql | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | | root | 127.0.0.1 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | | wordpressuser | 192.168.0.5 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | +---------------+-------------+-------------------------------------------+ 4 rows in set (0.00 sec)
3. Set a strong password for the MySQL root account and also existing user account :
Existing user account :
mysql> select Host,User,Password from user; +-------------+---------------+-------------------------------------------+ | Host | User | Password | +-------------+---------------+-------------------------------------------+ | localhost | root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | | mysql | root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | | 127.0.0.1 | root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | | 192.168.0.5 | wordpressuser | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | +-------------+---------------+-------------------------------------------+ 4 rows in set (0.00 sec)
Set new strong password :
mysql> set password for 'root'@'localhost'=password('newstrongpassword');
mysql> set password for 'root'@'127.0.0.1'=password('newstrongpassword');
mysql> set password for 'wordpressuser'@'192.168.0.5'=password('newstrongpassword');
4. Make sure logging such as general_log, slow_query_log and log-error has been enabled in mysql :
[root@mysql-server ~]# vim /etc/my.cnf
[mysqld] .. .. .. general_log_file=/var/log/mysql/mysqld.log general_log=1 slow_query_log_file=/var/log/mysql/mysqld.slow.log slow_query_log=1 [mysqld_safe] log-error=/var/log/mysql/mysqld.error.log ... ..
Create folder for mysql log and change the folder owner to mysql:
[root@mysql-server ~]# chown -R mysql:mysql /var/log/mysql
Verify the logs :
[root@mysql-server ~]# ll /var/log/mysql total 12 -rw-r----- 1 mysql mysql 3547 Apr 7 16:57 mysqld.error.log -rw-rw---- 1 mysql mysql 373 Apr 7 16:58 mysqld.log -rw-rw---- 1 mysql mysql 174 Apr 7 16:57 mysqld.slow.log
This Will help administrators to monitor critical events and helps in troubleshooting.
Reference : http://dev.mysql.com/doc/refman/5.7/en/server-logs.html
Once you have done above configuration, make sure yo restart the mysqld service :
[root@mysql-server ~]# service mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ]
Note : This configuration and hardening practice is very basic, you can fine tune your database based on your expected security level and also you can implement host iptables, physical firewall protection and operating system hardening in order to protect the MySQL server. You may refer to “Securing and Hardening Linux Dedicated Server”
How to Install WordPress on Remote MySQL
WordPress is an open source content management system (CMS) and popular blogging platform in the world based on PHP and MySQL platform. WordPress can be install on multiple way, either using dedicated server, virtual private server(VPS) or the cheapest way is running on shared hosting. When come to decision to run the wordpress on dedicated server or on VPS, the next question would be whether to run a web service and database service on single or multiple server. We have an option to combine it or to split it. For high performance wordpress website, i would suggest you to run web server(Apache, NGINX, Lighttpd) and database server (MySQL) on different server. Below steps should provide the basic steps how you can setup the wordpress on remote MySQL.
server1 = 192.168.0.5 = Apache server
server2 = 192.168.0.6 = Remote MySQL server
1. Login as a root on server1 then download latest wordpress file and extract the file :
[root@server1 html]# cd /var/www/html [root@server1 html]# wget http://wordpress.org/latest.tar.gz [root@server1 html]# tar xzvf latest.tar.gz
2. Login to server2, create the database for the wordpress :
[root@server2 ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.1.73 Source distribution 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> create database wordpressdb; Query OK, 1 row affected (0.00 sec) mysql> create user 'wordpressuser'@'192.168.0.5' identified by 'password'; Query OK, 0 rows affected (0.00 sec) mysql> grant all on wordpressdb.* to 'wordpressuser'@'192.168.0.5'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'wordpressuser'@'192.168.0.5'; +------------------------------------------------------------------------------------------------------------------------+ | Grants for wordpressuser@192.168.0.5 | +------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'wordpressuser'@'192.168.0.5' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' | | GRANT ALL PRIVILEGES ON `wordpressdb`.* TO 'wordpressuser'@'192.168.0.5' | +------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
3. Once step 2 above has been done, login again to server1, test the connectivity to database server :
[root@server1 html]# mysql -u wordpressuser -p -h 192.168.0.6 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.1.73 Source distribution 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 | | test | | worpressdb | +--------------------+ 3 rows in set (0.00 sec)
4. Still on server1, copy the config.php file :
[root@server1 ~]# cp /var/www/html/wordpress/wp-config-sample.php /var/www/html/wordpress/wp-config.php
5. Modify the config.php file and enter the database informations and remote mysql server details :
[root@server1 ~]# vi /var/www/html/wordpress/wp-config.php
define('DB_NAME', 'wordpressdb');
/** MySQL database username */
define('DB_USER', 'wordpressuser');
/** MySQL database password */
define('DB_PASSWORD', 'password');
/** MySQL hostname */
define('DB_HOST', '192.168.0.6');
/** Database Charset to use in creating database tables. */
define('DB_CHARSET', 'utf8');
/** The Database Collate type. Don't change this if in doubt. */
define('DB_COLLATE', '');6. To install, navigate the browser to http://servername/wordpress/.
http://192.168.0.5/wordpress/
How to Install and Securing MySQL on CentOS 6.4 VPS
MySQL Database server is one of the most popular used database in the internet especially for content management and blogging site. It’s can stores and retrieves data for the blog, websites and applications. This post will describes how you can install and securing MySQL on CentOS 6.4 virtual private server (VPS) or dedicated MySQL database server. For more information on MySQL, you can visit their website at www.mysql.com.
1. Install MySQL Database Server using yum command :
[root@centos64 ~]# yum install mysql mysql-server -y
Example :
[root@centos64 ~]# yum install mysql mysql-server -y Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: mirror.upsi.edu.my * epel: kartolo.sby.datautama.net.id * extras: mirror.upsi.edu.my * updates: mirror.upsi.edu.my Setting up Install Process Resolving Dependencies --> Running transaction check ---> Package mysql.x86_64 0:5.1.69-1.el6_4 will be installed --> Processing Dependency: mysql-libs = 5.1.69-1.el6_4 for package: mysql-5.1.69-1.el6_4.x86_64 ---> Package mysql-server.x86_64 0:5.1.69-1.el6_4 will be installed --> Processing Dependency: perl-DBI for package: mysql-server-5.1.69-1.el6_4.x86_64 --> Processing Dependency: perl-DBD-MySQL for package: mysql-server-5.1.69-1.el6_4.x86_64 --> Processing Dependency: perl(DBI) for package: mysql-server-5.1.69-1.el6_4.x86_64 --> Running transaction check ---> Package mysql-libs.x86_64 0:5.1.67-1.el6_3 will be updated ---> Package mysql-libs.x86_64 0:5.1.69-1.el6_4 will be an update ---> Package perl-DBD-MySQL.x86_64 0:4.013-3.el6 will be installed ---> Package perl-DBI.x86_64 0:1.609-4.el6 will be installed --> Finished Dependency Resolution Dependencies Resolved ==================================================================================================== Package Arch Version Repository Size ==================================================================================================== Installing: mysql x86_64 5.1.69-1.el6_4 updates 907 k mysql-server x86_64 5.1.69-1.el6_4 updates 8.7 M Installing for dependencies: perl-DBD-MySQL x86_64 4.013-3.el6 base 134 k perl-DBI x86_64 1.609-4.el6 base 705 k Updating for dependencies: mysql-libs x86_64 5.1.69-1.el6_4 updates 1.2 M Transaction Summary ==================================================================================================== Install 4 Package(s) Upgrade 1 Package(s) Total download size: 12 M Downloading Packages: (1/5): mysql-5.1.69-1.el6_4.x86_64.rpm | 907 kB 00:07 (2/5): mysql-libs-5.1.69-1.el6_4.x86_64.rpm | 1.2 MB 00:12 (3/5): mysql-server-5.1.69-1.el6_4.x86_64.rpm | 8.7 MB 01:30 (4/5): perl-DBD-MySQL-4.013-3.el6.x86_64.rpm | 134 kB 00:00 (5/5): perl-DBI-1.609-4.el6.x86_64.rpm | 705 kB 00:06 ---------------------------------------------------------------------------------------------------- Total 101 kB/s | 12 MB 01:57 Running rpm_check_debug Running Transaction Test Transaction Test Succeeded Running Transaction Updating : mysql-libs-5.1.69-1.el6_4.x86_64 1/6 Installing : perl-DBI-1.609-4.el6.x86_64 2/6 Installing : perl-DBD-MySQL-4.013-3.el6.x86_64 3/6 Installing : mysql-5.1.69-1.el6_4.x86_64 4/6 Installing : mysql-server-5.1.69-1.el6_4.x86_64 5/6 Cleanup : mysql-libs-5.1.67-1.el6_3.x86_64 6/6 Verifying : mysql-libs-5.1.69-1.el6_4.x86_64 1/6 Verifying : perl-DBD-MySQL-4.013-3.el6.x86_64 2/6 Verifying : perl-DBI-1.609-4.el6.x86_64 3/6 Verifying : mysql-server-5.1.69-1.el6_4.x86_64 4/6 Verifying : mysql-5.1.69-1.el6_4.x86_64 5/6 Verifying : mysql-libs-5.1.67-1.el6_3.x86_64 6/6 Installed: mysql.x86_64 0:5.1.69-1.el6_4 mysql-server.x86_64 0:5.1.69-1.el6_4 Dependency Installed: perl-DBD-MySQL.x86_64 0:4.013-3.el6 perl-DBI.x86_64 0:1.609-4.el6 Dependency Updated: mysql-libs.x86_64 0:5.1.69-1.el6_4 Complete!
2. Make mysqld daemon start at boot and start MySQL Database Server :
[root@centos64 ~]# chkconfig mysqld on
[root@centos64 ~]# service mysqld start
Initializing MySQL database: 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 centos64.ehowstuff.local 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. Securing MySQL Database Server. This includes setting up the password for mysql root, remove anonymous users, disallow root login remotely and remove test database and access.
[root@centos64 ~]# /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!
4. For testing, login to MySQL Server using defined password :
[root@centos64 ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.1.69 Source distribution 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 | +--------------------+ 2 rows in set (0.01 sec) mysql>