mysql setting backup

$ mysqldump -h localhost -u admin -p mydb > mydb_backup.sql

all databases
$ mysqldump --all-databases -u admin -p alldb_backup.sql

restore database
$ mysqladmin -u admin -p create mydb
$ mysql -h localhost -u admin -p mydb < mydb_backup.sql

to restore all databases
$ mysql -h localhost -u admin -p < alldb_backup.sql

MySQL provides a very general tool, mysqldump, to export all data from the database server. This tool can be used with any type of database engine, be it MyISAM or InnoDB or any other.

To perform an online backup of InnoDB tables, mysqldump provides the --single-transaction option. With this option set, InnoDB tables will not be locked and will be available to other applications while backup is in progress.


Oracle provides the MySQL Enterprise backup tool for MySQL Enterprise edition users. This tool includes features such as incremental and compressed backups. Alternatively, 

Percona provides an open source utility known as Xtrabackup. It provides incremental and compressed backups and many more features.Some other backup methods include copying MySQL table files and the mysqlhotcopy script for InnoDB tables. For these methods to work, you may need to pause or stop the MySQL server before backup.

You can also enable replication to mirror all data to the other server. It is a mechanism to maintain multiple copies of data by automatically copying data from one system to another. In this case, the primary server is called Master and the secondary server is called Slave. This type of configuration is known as Master-Slave replication. Generally, applications communicate with the Master server for all read and write requests. The Slave is used as a backup if the Master goes down. Many times, the Master-Slave configuration is used to load balance database queries by routing all read requests to the Slave server and write requests to the Master server. Replication can also be configured in Master-Master mode, where both servers receive read-write requests from clients.

Comments

Popular posts from this blog

HAproxy logging

teamcity Automatic Agent Start under Linux

NFS mount add in fstab _netdev instead of default | firewall-cmd --list-all