Posts Tagged ‘sql databases’

Make daily Linux MySQL database backups with shell script

Thursday, May 23rd, 2013

Creating database backup with MySQL with mysqlbackupper and mysqlback shell scripts easy create mysql backups

Some time ago, I've written a tiny shell script which does dumps of Complete (SQL Script) MySQL databases. There are plenty of ways to backup MySQL database and plenty of scripts on the net but I like doing it my own way. I have few backup scripts. I prefer script database over keeping binary logs, or using some un-traditional backup methods like backing all binary data in /var/lib/mysql.

One was intended to backup with mysqldump whole database and later upload to a central server running tsh (shell). Using tsh maybe not the best method to upload, but the script can easily be modified to use ssh passwordless authentication as a method to upload.

I'm not a pro shell scripter, but MySQLBackupper script can be used as useful for learning some simple bash  shell scripting.

To use the script as intended you will have to build tsh from source. Tsh is in very early development stage (ver 0.2) but as far as I tested it before some years it does great what it is intended for. You can  MySQLBackupper.sh script from here.
Earlier, I used MysqlBackupper.sh to upload all SQL dumps to /backups directory on central backup storage server, thus I had written secondary script to classify uploaded backups based on backup archive name. Script used is mysqldumps-classify.sh and can be viewed here. Though this way of making backups, needs a bit of custom work for managing backups up to 10 / 20 servers it worked well.

I have written also another mysqlbackup script which is much more simplistic and only dumps with mysqldump and stores copies on hard disk in tar.gz archive. You can download my other simple mysqkbackup.sh here.

Only inconvenient thing about above scripts is they dump all SQL databases. Hence whether necessary to get content for single database from (complete) All database SQL (script backup), I use SED (stream editor) one liner script.

It is interesting to hear how others prepare their MySQL db backups.

Fixing MySQL server start up “ERROR 1045 (28000): Access denied for user ‘debian-sys-maint’@’localhost’ (using password: YES)”

Friday, September 7th, 2012

I'm still busy configuring the new Lenovo (Le-novo) 🙂 ThinkCenter server necessery for migration of old machines. I've done a lot yesterday but really moving all this stuff takes time …

I moved the SQL databases from the old MySQL server host to the new Debian Linux host.

In order to move databsaes, I did the usual SQL dump from current working host with:

mysql:~# mysqldump --opt --allow-keywords --add-drop-table --all-databases -u root > dump.sql

After that did the standard move of dump.sql to the new host with sftp

mysql-new:~# sftp root@mysql-host
Connected to mysql-host.
sftp> get dump.sql
....
sftp> exit

and imported dump:

mysql-new:~# mysql -u root -p < dump.sql
Enter password:

Databases dump grow really a lot (7GB)!, so I had to wait for dump.sql to import about 20 minutes – (the host configuration is Dual core 6Ghz 3MB Cache CPU, 4GB DDR3 RAM, 7200 500 GB Hitachi ExcelStor Techno V32O HDD).

The dumps migration was between identical release Debian Linux – (Squeeze 6.0.5) servers running identical versions of MySQL.

mysql-new:~# mysql --version
mysql Ver 14.14 Distrib 5.1.63, for debian-linux-gnu (i486) using readline 6.1

Because of that the whole db import worked like a charm.

Once moved the SQL started re-started normally but there was an on screen warning:

ERROR 1045 (28000): Access denied for user
'debian-sys-maint'@'localhost' (using password: YES)"

The cause of this warning error is because of way /etc/init.d/mysql script is written and in particular the custom MySQL (Debian specific start-up philosophy).

/etc/init.d/mysql is written in a way that on every restart a check of Database consistency is done. There in the script the user debian-sys-maint (a user with mysql administrator root privileges) is used to do the quick consistency check. The debian-sys-maint password which is used on start-up is stored in /etc/mysql/debian.cnf:

mysql-new:~# less /etc/mysql/debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host = localhost
user = debian-sys-maint
password = pQFM9RetOHFjewwn
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host = localhost
user = debian-sys-maint
password = pQFM9RetOHFjewwn
socket = /var/run/mysqld/mysqld.sock
basedir = /usr

The whole problem is that during, the old SQL import the password set for user debian-sys-maint is different and once SQL starts the init script reads this pass and fails to login to SQL server.

The warning (error):

ERROR 1045 (28000): Access denied for user
'debian-sys-maint'@'localhost' (using password: YES)
hence appears on every SQL start (including on every system boot). The err is generally harmless and SQL seems to work fine with or without it. However since the consistency check is not done at start up, if there are some CORRUPT tables not initiating the start up check is not a good idea.

There are two options to get rid of the warning one and better one is to check in /etc/mysql/debian.cnf for password string and change the pwd with mysql cli e.g.:

new-mysql:~# grep -i pass /etc/mysql/debian.cnf | uniq
password = pQFM9RetOHFjewwn

GRANT SELECT on `mysql`.`user` to 'debian-sys-maint'@'localhost' identified by 'pQFM9RetOHFjewwn';
Query OK, 1 row affected (0.01 sec)
GRANT SELECT ON mysql.user TO 'debian-sys-main'@'localhost';Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Second option (not recommended in terms of security) is to set user/pass to root values in /etc/mysql/debian.cnf.

That's all. N-joy 🙂