≡ Menu

mysqldump command

mysqldump: Copy Database Using Shell Pipes

How do I use mysqldump command with Unix shell pipes?
[click to continue…]

WordPress Rename Image URL For a CDN Usage

Recently, we moved all our static assets to a CDN (Content Delivery Network) as described here. However, we have over 8000+ old blog posts and we need to point static images url to our CDN account hosted at http://MyACCOUNT.cloudfront.net/images/ folder. How do I rename all image urls stored in wordpress posts? How do I offload static images to a CDN urls under WordPress?
[click to continue…]

I'm trying to make backups using the mysqldump command and getting the following error or warning:

mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES

How do I fix this problem?
[click to continue…]

Restore a backup of a MySQL Database Server

Q. How can I restore a backup of a MySQL database server made with mysqldump program discussed here?

A. You can use standard mysql - the MySQL command-line tool to restore a backup of a MySQL database server.

Read back dump file

You can read the dump file back into the server like this:
mysql db-name < backup-file.sql To restore database called sales, first create the database sales: $ mysql -u root -p
Now create database called sales using SQL statement:

mysql> CREATE DATABASE sales;
mysql> quit;

Now restore database, enter:
$ mysql -u root -p sales < /path/to/sales-backup-file.sql

Q. I'm moving my server from local data center to another data center. I've new server up and running. My site is dynmic and I'd like to transfer MySQL database from old server to new server. How do I make transfer smooth and successful?

A
. The best and secure way is to use ssh to transfer database to another server. You need to use following tools:

=> SSH client
=> SSH Server on remote box
=> mysqldump command
=> UNIX pipes

Let us say you would like to Transfer MySQL database called foo to remote box called bar, enter the following command at shell prompt:
$ mysqldump foo | ssh user@remote.box.bar.com mysql foo

You can just copy table called chocolate (from recipe database) to remote database called icecream using same syntax:
$ mysqldump recipe chocolate | ssh user@remote.box.com mysql icecream

Ubuntu Linux Backup MySQL Server Shell Script

Q. I’m new to Linux and I’ve dedicated VPS server running Ubuntu Linux. I’m using CMS software and MySQL act as database server. Can you explain how can I backup all mysql server databases to ftp server IP address called 10.1.5.2?

A. You can use mysqldump command to backup database. The mysqldump client is a backup program. It can be used to dump a database or a collection of databases for backup or for transferring the data to another SQL server. The dump contains SQL statements to create the table or populate it, or both.

Once database is dumped, you need to upload the same to ftp server. Use lftp client to upload all files.

Install lftp

lftp is a file transfer program that allows sophisticated ftp, http and other connections to other hosts. If site is specified then lftp will connect to that site otherwise a connection has to be established with the open command. To install lftp, enter:

sudo apt-get install lftp

Shell script to backup MySQL database server

Following is the shell script. It will dump all database to /backup/mysql and later it will upload to FTP server. You need to setup correct username and password before using the script:

 
#!/bin/bash
### MySQL Server Login Info ###
MUSER="root"
MPASS="MYSQL-ROOT-PASSWORD"
MHOST="localhost"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
BAK="/backup/mysql"
GZIP="$(which gzip)"
### FTP SERVER Login info ###
FTPU="FTP-SERVER-USER-NAME"
FTPP="FTP-SERVER-PASSWORD"
FTPS="FTP-SERVER-IP-ADDRESS"
NOW=$(date +"%d-%m-%Y")
 
### See comments below ###
### [ ! -d $BAK ] && mkdir -p $BAK || /bin/rm -f $BAK/* ###
[ ! -d "$BAK" ] && mkdir -p "$BAK"
 
DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
do
 FILE=$BAK/$db.$NOW-$(date +"%T").gz
 $MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
done
 
lftp -u $FTPU,$FTPP -e "mkdir /mysql/$NOW;cd /mysql/$NOW; mput /backup/mysql/*; quit" $FTPS

Save script as /home/your-name/mysql.backup.sh file. Setup executable permission:
$ chmod +x /home/your-name/mysql.backup.sh
To backup MySQL, enter:
/home/your-name/mysql.backup.sh
OR
sudo /home/your-name/mysql.backup.sh

Run MySQL backup script as cron job

To automate procedure setup a cron job. For example run backup everyday at midnight (i.e once a day), enter:
$ sudo crontab -e
Append following cron job:
@midnight /home/you/mysql.backup.sh >/dev/null 2>&1
Save and close the file. Please note that above script should work with other Linux distros or UNIX like oses.

Q. I made MySQL backup using mysqldump -u user -h localhost -ppassword forumdb | -gzip -9 > forumdb.sql.gz command. How do I restore MySQL database backup using a shell prompt under Linux / UNIX operating system?
[click to continue…]