mysql command to backup database-PHP Tutorial-php.cn

mysql command to backup database

WBOY

Release： 2016-07-25 08:50:58

Original

949 people have browsed it

1. mysql database backup command
MySQL database backup using command line
For example:
Database address: 127.0.0.1
Database username: root
Database password: pass
Database name: myweb
Back up the database to D drive and directory
mysqldump -h127.0.0.1 -uroot -ppass myweb > d:/backupfile.sql
Back up to the current directory. Back up the MySQL database in a format with a delete table, which allows the backup to overwrite the existing database without manually deleting the original database
mysqldump --add-drop-table -h127.0.0.1 -uroot -ppass myweb > backupfile.sql
Directly compress and back up the MySQL database to the D drive and directory
mysqldump -h127.0.0.1 -uroot -ppass myweb | gzip > d:/backupfile.sql.gz
Back up a certain table(s) of the MySQL database. This example backs up the table1 table and table2 table. Back up to /home of the Linux host
mysqldump -h127.0.0.1 -uroot -ppass myweb table1 table2 > /home/backupfile.sql
Back up multiple MySQL databases at the same time
mysqldump -h127.0.0.1 -uroot -ppass --databases myweb myweb2 > multibackupfile.sql
Only the database structure is backed up. Back up the database named myweb and the database named myweb2 at the same time
mysqldump --no-data -h127.0.0.1 -uroot -ppass --databases myweb myweb2 > structurebackupfile.sql
Back up all databases on the server
mysqldump --all-databases -h127.0.0.1 -uroot -ppass > allbackupfile.sql
Command to restore MySQL database. Restore the current backup database named backupfile.sql
mysql -h127.0.0.1 -uroot -ppass myweb < backupfile.sql
Restore compressed MySQL database
gunzip < backupfile.sql.gz | mysql -h127.0.0.1 -uroot -ppass myweb
Move the database to the new server. This example is to copy the local database myweb to the remote database named serweb, where the remote database must have a database named serweb
mysqldump -h127.0.0.1 -uroot -ppass myweb | mysql --host=***.***.***.*** -u database username -p database password -C serweb
2. Use multiple backup software
(1) Open the MySQL database backup and fill in the database related parameters.
IP: Default 127.0.0.1; Domain name: localhost
Username: the account name used to log in to the database
Password: Password to log in to the database
Port number: Default 3306, or find mysql.ext and open it, enter the password and press Enter, enter "show global variables like 'port';" after mysql> and press Enter
mysql command to backup database

(2) After filling in the parameters, click "Next", check the database that needs to be backed up, and click "Finish". The task will be created (the files will start to be automatically backed up)
mysql command to backup database

(3) Click "Set Frequency" under "Operation" to set the task backup frequency; "Edit Parameters" to set the user name, password, and port number for connecting to the database; "Backup Content" reselects the database that needs to be backed up.
mysql command to backup database

Tip: If you need to create another database backup task, click "Backup Content" under "Operation", click "Add Database", and then follow the same steps as when creating the first database task.
(If you are prompted that the number of tasks is not enough, please go to the official website to redeem the number of tasks; only one of the same database can be created)
mysql command to backup database

3.3. SQL Server database backup
(1) Open the MSSQL database and fill in the relevant parameters;
Instance name: Under normal circumstances, all instance names will be displayed for you to choose. Click "Edit" to fill in manually
Username: the account name used to log in to the database
Password: Password to log in to the database
mysql command to backup database

(2) Other steps are the same as backing up the MySQL database, as above.
Summary: Comparing the above two methods, it is obviously easier to use multiple backup software. Not having to manually back up saves a lot of time and energy; and using multiple backups allows you to monitor the backup status in real time and solve problems immediately if you encounter them. The final result of the backup will also be notified to the user by email, so you don’t have to worry about whether the backup was successful when restoring. If you are interested, just use it quickly!