Backup MySQL in command line with compression

For those who looking for way to backup mysql database,
you can use mysqldump to backup mysql database.

Below is the script example to backup mysql database in command line:-

$ mysqldump -h localhost -u username -p password database_name > backup_db.sql

If your mysql database is very big, you might want to compress your sql file.
Just use the mysql backup command below and pipe the output to gzip,
then you will get the output as gzip file.
Read more…

Export MySQL data into CSV or PSV files

One way to export data from MySQL tables into plain text files would be to use mysqldump, then remove SQL from the dump file. Perhaps an easier way is to pipe SQL SELECT output to a file.

For CSV (comma delimited, enclosing content in quotes):

mysql mydb -e "SELECT * FROM my_table" | sed 's/\t/","/g;s/^/"/;s/$/"/;' > my_db.my_table.csv

Example output:

"column1","column2"
"value1","value2"
Read more…

How to set up master-slave replication in MySQL

Before we go into how to set up master-slave replication in MySQL, let us talk about some of the reasons I have set up master-slave replication using MySQL.

1) Offload some of the queries from one server to another and spread the load: One of the biggest advantages to have master-slave set up in MySQL is to be able to use master for all of the inserts and send some, if not all, select queries to slave. This will most probably speed up your application without having to diving into optimizing all the queries or buying more hardware.

2) Do backups from slave: One of the advantages people overlook is that you can use MySQL slave to do backups from. That way site is not affected at all when doing backups. This becomes a big deal when your database has grown to multiple gigs and every time you do backups using mysqldump, site lags when table locks happen. For some sites, this could mean that site goes down for few secs to minutes. If you have slave, you just take slave out of rotation (should be built into code) and run backups off the slave. You can even stop slave MySQL instance and copy the var folder instead of doing mysqldump.
Read more…

How To remote backup with rsync

This is probably the simply way to backup data to other server using a secure protocoal an minimum bandwidh.

Note:
BackupServer.com – Is the backup server
MainServer.com – Is the server you want to backup

1. Login to the server you want to backup
2. Creating a key with the ssh-keygen utility:
   ssh-keygen -t rsa -N '' (these are two single quotes)
3. Copy RSA encryption key to the backup server:
   scp ~/.ssh/id_rsa.pub username@backupserver.com:keys/server1
ssh username@backupserver.com mergekeys

Read more…

Shell Script To Dump All MySQL Databases Every Hour To NAS Storage

+ Download this script
+ Modify settings according to your setup
+ Install cron job as follows to run script every hour

# Backup database every 1 hr to folder /nas/mysql
@hourly /root/scripts/db1hr.backup.sh >/dev/null 2>&1

Sample Shell Script To Dump All MySQL Databases
Read more…

Backup a database using phpMyadmin

Backup of Mysql database
It is assumed that you have phpMyAdmin installed since a lot of web service providers use it.

1. Open phpMyAdmin.
2. Click Export in the Menu to get to where you can backup you MySql database. Image showing the export menu.
3. Make sure that you have selected to export your entire database, and not just one table. There should be as many tables in the export list as showing under the database name.
4. Select”SQL”-> for output format, Check “Structure” and “Add AUTO_INCREMENT” value. Check “Enclose table and field name with backquotes”. Check “DATA”, check use “hexadecimal for binary field”. Export type set to “INSERT”.
5. Check “Save as file”, do not change the file name, use compression if you want. Then click “GO” to download the backup file.

Read more…

MySQL Dump and Restore

Dump ALL MySQL Databases:

mysqldump –-user=XXXXXXXX -–password=XXXXXXX -A > /PATH/TO/DUMPFILE.SQL

Dump Individual or Multiple MySQL Databases:

mysqldump -–user=XXXXXXXX -–password=XXXXXXX DB_NAME1 DB_NAME2 DB_NAME3 > /PATH/TO/DUMPFILE.SQL
Read more…