If you are on a cPanel server, you can restore a database about in 2GB of size, though default allowed size is 50MB, which can be increased through tweak settings
cPanel PHP max POST size (Minimum: 55; Maximum: 2,047)
cPanel PHP max upload size (Minimum: 50; Maximum: 2,047)
In other cases, where you are to restore huge databases, consider following below.
Perform below on Source Server.
Simplest way to create dump
mysqldump db_u_want_to_generate_dump > /root/source_db.sql
While dumping, for skipping or ignoring a specific table in the db
mysqldump db_name –ignore-table=db_name.table_name
or
mysqldump db_name –ignore-table=db_name.table_name > export.sql
You may also dump a single table as below
mysqldump db_name table_name > sigleexport.sql
Later download the dump and upload in the same location of target server.
Sometimes, one may need to restore a specific table which could be big in size, so better create its separate dump and restore then.
creating dump for a specific table from the dump of a big database
sed -n -e ‘/DROP TABLE.*`source_db_table-specific`/,/UNLOCK TABLES/p’ source_db.sql > separated_table.sql
To make restoration process faster, Follow below.
Better if you will add below lines in the my.cnf file of target machine.
key_buffer_size=16G
innodb_buffer_pool_size=20G
innodb_log_file_size=3G
innodb_log_buffer_size=128M
innodb_flush_log_at_trx_commit=0
innodb_thread_concurrency=0
We need to write some code in mysqldump we already have to process restoration faster so we must have to run a bash script to write some comments with in mysql dump file. To do that we may have to go through as below.
How to create an empty text file in putty?
touch file.txt or file.sh (.sh if you want to run bash script)
Make it executable by updating file permissions.
chmod u+x file.sh
Run below to execute
./file.sh
insert below code via vi editor
Mentioning source_db.sql because we are restoring all the tables, if we are to restore a single big table source_db.sql will be replaced with source_db_table-specific.sql
———————
#!/bin/bash
echo “SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;” > source_db.sql
mysqldump -u db_usrname –password=userpass db_name >> source_db.sql
echo “COMMIT;” >> source_db.sql
———————
then run the bash script by inserting below command.
./file.sh
to verify the authenticity of dump
(head;tail) < /root/source_db.sql
To restore the dump run below command
mysql -u db_user -p db_name < source_db.sql
In the meanwhile, if you want to connect your target account on new server with db on old server, follow below.
To connect mysql remotely disable below on source server my.cnf
#bind-address=127.0.0.1
#skip-networking
Some other factors you can utilize for dumping a db.
For creating dump for specific tables of database
mysqldump -u source_username -p db_source-main tbl1 tbl2 tbl3 > source_db.sql
Ignoring a specific table while dumping a database
mysqldump -u root -p source_db.sql –-ignore-table=my_db.table_to_ignore > source_db.sql
To dump large database
mysqldump -u root(source_username) -p my_large_db –single-transaction –quick > my_large_source_db.sql
For creating dump of single or multiple databases
mysqldump -u username -p –databases db_1 db_2 db_3 > source_db.sql
Leave a Reply