MySQL handly commands

To prevent duplicate entry issue - replace INSERT into with REPLACE INTO . DEF: REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. REPLACE into table (id, name, age) values(1, "A", 19)
Run Optimize of All tables of a database: mysqlcheck -o db_to_optimize -udb_user -p
All databases mysqlcheck -o --all-databases -udb_user -p
Get the size of tables in a database The query gets the size (in MB) of each table in the specified database name. SELECT table_name , round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"
FROM information_schema.TABLES
WHERE table_schema = 'DATABASE_NAME';
MySQLDump as a gzipped file mysqldump mycrm -u db_root -p db_root_pass | gzip -c | cat > mycrm-$(date +%Y-%m-%d-%H.%M.%S).sql.gz
Copy table from one database to another mysqldump --user=user1 --password=password1 database1 table1 \
| mysql --user=user2 --password=password2 database2
-- http://dev.mysql.com/doc/refman/5.0/en/replace.html http://dev.mysql.com/doc/refman/5.0/en/mysqlcheck.html http://stackoverflow.com/questions/9620198/how-to-get-the-sizes-of-the-tables-of-a-mysql-database http://sheldonrcohen.com/Blog/tabid/88/EntryId/3/Easy-one-liner-MySql-dump-database-to-a-tar-gz-file.aspx https://stackoverflow.com/questions/12242772/easiest-way-to-copy-a-table-from-one-database-to-another