MySQL

How to Backup MySQL Database

Posted on
Backup MySQL Database MySQL provides mysqldump utility to let us dump SQL statements that can recreate the whole database. We can output the SQL statements to a plain text file, furthermore, a compressed file, or we can pipe the output to feed any other live database. Here in this post, we introduce some use cases […]
Linux

How to Migrate Local Databases to Remote Host or Vice Versa Without Dump Files

Posted on
For migrating a remote database, you can dump and pipe the whole database from remote server to local server via ssh tunnel without intermediate dump files.[root@test ~]# ssh 10.10.10.1 “mysqldump –routines -u root -p’xxxxxxxx’ chicago_db” | mysql -u root -p’xxxxxxxx’ -D boston_dbVice versa, you can migrate a local database to the remote server.[root@test ~]# mysqldump […]
MySQL

How to Backup MySQL Data and Stored Procedures

Posted on
By default, mysqldump only backup the data from which database indicated in the command line. For stored procedures, You have to enable the backup by adding an option –routines in the mysqldump command line. For example:[root@test ~]# mysqldump –routines -u root -p database_name > dump.sqlWhich routines oly include:ProceduresFunctionsIf you additionally need events to be backed […]
MySQL

How to Resolve Error 1712: Index table_name is corrupted

Posted on
Got this error when using mysqldump for backing up a database. At this moment, we can’t do any operations but DDL.mysqldump: Error 1712: Index table_name is corrupted when dumping table `table_name` at row: 0Or met this error when queried the table.ERROR 1712 (HY000): Index table_name is corruptedIn the log, you can also see the errors.[root@test […]
MySQL

How to Minimize the Impact of Mysqldump

Posted on
If you have a very big database containing big tables to be backed up, you might have the same issue with mine: The web server looks like to be dead during MySQL dumping (mysqldump), and the visitors get no response from the database for a long long time.[root@test ~]# mysqldump -u root -p big_database | […]
MySQL

How to Backup Selected Tables by mysqldump

Posted on
There’re two ways to backup selected tables in MySQL:Positive ListingYou can backup tables by positively and explicitly listing all necessary ones. The syntax is as following:mysqldump -h <host> -u <username> -p <db_name> <table_name> …You may skip the host option -h if you operate in the localhost. For example:[root@localhost ~] mysqldump -u root -p db1 table1 table2 […]
MySQL

How to Resolve Very Big Dump File of MySQL

Posted on
I usually backup the database by mysqldump, but the plain SQL dump file was getting larger and larger and took most of the free space. Finally, I found a solution to this problem. To backup the database, we can directly pipe the output stream to a zip file, no intermediate dump file involved: [root@test ~]# […]
MySQL

How to Resolve mysqldump: Couldn’t execute ‘SHOW TRIGGERS LIKE XXX”: Got error 28 from storage engine (1030)

Posted on
I met the error when I use mysqldump to backup the database.mysqldump: Couldn’t execute ‘SHOW TRIGGERS LIKE ‘XXX”: Got error 28 from storage engine (1030)I thought it was open_files_limit issue, but it’s not, 5000 seems enough to this database,mysql> show variables like ‘open_files_limit’;+——————+——-+| Variable_name    | Value |+——————+——-+| open_files_limit | 5000  |+——————+——-+1 row in set […]
MySQL

How to Dump a Remote Database over SSH in Windows

Posted on
First of all, you have to ensure that SSH command utility has been installed, because Windows does not provide it by default.C:Usersed>ssh’ssh’ is not recognized as an internal or external command,operable program or batch file.But, you can download and install OpenSSH for Windows from SourceForge like mine.After you installed OpenSSH in Windows, you can test […]
MySQL

How to Import From a Remote MySQL Database in Windows

Posted on
You can pipe the remote dump result into the local database in Windows like this:C:Usersed>mysqldump -u root -p -h db.example.com database_name | mysql -u root -p database_nameEnter password: Enter password: ********************…Please note that, there are two passwords required to be entered in the above example. You can also include the two passwords in the command […]