Home >> Exporting large database on phpMyAdmin

Tag: mysql

Exporting large database on phpMyAdmin

Exporting large database from most web hosting company can be quite problematic due to their server timeout limit setting.

To get around this problem one alternative solution is to check which table has the most size and export it individually, then you can export the rest of the table by excluding the one you already exported it before.

MySQL Basic Command Cheat Sheet

# Create user
CREATE USER 'username'@'hostname' IDENTIFIED BY 'mypassword';

# Show user
SELECT username FROM mysql.user;

# Grant privilege
GRANT ALL PRIVILEGES ON *.* TO 'username'@'hostname';
Or
GRANT SELECT, INSERT, UPDATE, DELETE ON database.table TO 'username'@'hostname'

# Revoke / remove privilege
REVOKE ALL PRIVILEGES ON object FROM username

# Dump data
mysqldump -h hostname -u root -p databasename > filename.txt

# Import data
mysql -u username -p databasename < sqlfile.sql

# Alter to fulltext
ALTER TABLE tablename ADD FULLTEXT(fieldname);

# Alter character set and collation, CONVERT change all columns
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# Show character set of a table
SHOW FULL COLUMNS FROM tablename

# Show table option when creating that table
SHOW CREATE TABLE tablename;

# Show all table status
SHOW TABLE STATUS;