Category Archives: MySQL

Repair and check all tables mysqlcheck -u root -p –auto-repair –check –optimize –all-databases

disable query caching SELECT sql_no_cache count(*) AS Count FROM DataTable WHERE Field1 = 1 AND IndexField1 = ‘B’ AND Field2 = ’1′

If you have data saved in MySQL as ISO8859-1 and you want to converted it to UTF8. You can change the character set of your connection with the following query: SET CHARACTER SET UTF8

Copy compleet table layout including keys in one query: CREATE TABLE newtable LIKE oldtable;

The following command dumps the database with replication position. You can import this file on the slave server. The replication will replicate all changes after this dump automatically because of the replication position in the dumpfile. mysqldump -h 127.0.0.1 -p … Continue reading →

run the following commands to skip a query in the replication. SHOW SLAVE STATUS \G STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;

You can issue multiple ADD, ALTER, DROP, and CHANGE clauses in a single ALTER TABLE statement, separated by commas. This is a MySQL extension to standard SQL, which permits only one of each clause per ALTER TABLE statement. For example, … Continue reading →

Do one action in the trigger CREATE TRIGGER testref BEFORE INSERT ON test1   FOR EACH ROW     INSERT INTO test2 SET a2 = NEW.a1; Do more actions in one trigger delimiter |   CREATE TRIGGER testref BEFORE INSERT … Continue reading →

Copy data from tb1 to tb2 INSERT INTO tb2 SELECT * FROM tb1 Copy specific columns from tb1 to tb2 INSERT INTO tb2 (col1,col2) SELECT col11,col12 FROM tb1 Copy specific columns from tb1 to tb2 with WHERE statement INSERT INTO … Continue reading →

dump without locking: mysqldump –single-transaction –quick -p database > database.sql