Monday, April 6, 2015

Daily backup MySQL database to another computer



Assume MySQL server is example.ca, I want to dump MySQL database test to dev2.ca daily.
 1) In PHPMyAdmin, click Users, add new user jiansenbk and  host dev2.ca and  database test
grant all
2) In dev2.ca
create MySQLdump1.sh (no line break below)
mysqldump  -h example.ca -u jiansenbk -pPass2 test
| gzip >/home/jiansen/mysqldump/MySQLDB_`date +"%Y%m%d"`.sql.gz

3) chmod +x MySQLdump1.sh

4) crontab -e
and enter (no linebreak, chnage server and user name password and database to yours)
45 23 * * * /home/jiansen/mysqldump/MySQLdump1.sh 2>&1>>/home/jiansen/mysqldump/mysqlbackup.log


Every night 11:45. MySQL database test is dump to /home/jiansen/mysqldump with different timestamps
Video: Daily backup MySQL database to another computer

Add login screen and second user login in PHPMyAdmin




1) Default  PHPMyAdmin without login screen. To add  login screen:
Go to Users, find root@localhost and edit privileges and change password. Use same password in XAMPP configuration

2) Under xampp/phpMyAdmin/config.inc.php
change
$cfg['Servers'][$i]['auth_type'] = 'config';
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = '';
$cfg['Servers'][$i]['AllowNoPassword'] = true;

to
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = 'test';
$cfg['Servers'][$i]['AllowNoPassword'] = false;


3) To add second name and password in login scrren:
Go to Users tab in PHPMyAdmin, Add new User, type username and password, select host as localhost and check all and click go.

Video: Add login screen and second user login  in PHPMyAdmin