Thursday, August 17, 2017

Set up automated mysql/mariadb backup

We are going to use mysqldump for this automated backup exercise.


Create a read only user in mysql to do the backup, in this case, backupuser
# mysql -uroot -p
mysql> grant lock tables, select on *.* to 'backupuser'@'%' identified by 'password_of_your_choice';

Test doing mysqldump using the new user
# mysqldump -ubackupuser -p --all-databases > /tmp/mysql_all_db.sql

If everything is fine, and the mysql_all_db.sql is created successfully, proceed on giving the backupuser passwordless access to mysql. Add below lines to /root/.my.cnf
[mysqldump]
user = backupuser
host = localhost
password = "password_of_your_choice"

Test whether you can still do mysqldump without the -u and -p options
# mysqldump --all-databases > /tmp/mysql_all_db.sql

If everything is running fine, now is the time to add to crontab
# crontab -e

Put below lines to run the mysqldump command at 11 PM every Saturday, and the filename will be datestamped as well
* 23 * * * Sat /usr/bin/mysqldump --all-databases > /backup/mysql_all_db-$(date +%Y-%m-%d).sql

Friday, July 14, 2017

How to install phpmyadmin on Linux Centos 7

ssh into your centos 7 box, using root (assuming your centos box ip is 10.0.0.100)
# ssh root@10.0.0.100
 
Install apache web server (httpd)
# yum install httpd
 
Install php, and php-mysql
# yum install php php-myqsl
 
Start apache webserver
# systemctl start httpd
 
Install mariadb-server
# yum install mariadb-server
 
Start mariadb-server
# systemctl start mariadb
 
Secure mariadb-server installation (will set root password, and secure mariadb-server installation)
# mysql_secure_installation 
 
Install epel repo (epel stands for extra package for enterprise linux) 
# yum install epel-release 
 
Install phpmyadmin
# yum install phpmyadmin

Set phpmyadmin to allow ip from local lan (in this case, the local lan ip segment is 10.0.0.0/24), by changing below lines in /etc/httpd/conf.d/phpMyAdmin.conf
# diff -u phpMyAdmin.conf /etc/httpd/conf.d/phpMyAdmin.conf 
--- phpMyAdmin.conf     2017-07-13 19:24:52.310000000 +0800
+++ /etc/httpd/conf.d/phpMyAdmin.conf   2017-07-13 19:15:50.366000000 +0800
@@ -14,7 +14,7 @@
    
      # Apache 2.4
      
-       Require ip 127.0.0.1
+       Require ip 10.0.0
        Require ip ::1
      
    
@@ -22,7 +22,7 @@
      # Apache 2.2
      Order Deny,Allow
      Deny from All
-     Allow from 127.0.0.1
+     Allow from 10.0.0
      Allow from ::1

@@ -31,7 +31,7 @@
    
      # Apache 2.4
      
-       Require ip 127.0.0.1
+       Require ip 10.0.0
        Require ip ::1
      
    
@@ -39,7 +39,7 @@
      # Apache 2.2
      Order Deny,Allow
      Deny from All
-     Allow from 127.0.0.1
+     Allow from 10.0.0
      Allow from ::
 
Restart httpd
# systemctl restart httpd 

Using your favorite browser, browse to http://10.0.0.100/phpmyadmin, assuming your server ip address is 10.0.0.100 
 

Login using your mysql root and password, that has been set in mysql_secure_installation step
 

Done