Mar posted 14 Mar 2009 and tagged mysql
Duplicate a table:
mysql> CREATE TABLE duplicate_name SELECT * FROM original_name;
Pop out
1 | mysql> CREATE TABLE duplicate_name SELECT * FROM original_name; |
Set root password:
mysql> UPDATE mysql.user SET Password = PASSWORD('password') WHERE User = 'root';
mysql> FLUSH PRIVILEGES;
Pop out
1 2 | mysql> UPDATE mysql.user SET Password = PASSWORD('password') WHERE User = 'root'; mysql> FLUSH PRIVILEGES; |
Dump MySQL database routines only:
mysqldump -u user_name -p --routines --no-data --no-create-db --no-create-info database_name > routines.sql
Pop out
$ | mysqldump -u user_name -p --routines --no-data --no-create-db --no-create-info database_name > routines.sql |
Limit rows in mysqldump:
mysqldump --where="true LIMIT 10000" -u user_name -p database_name table_name > table_name_10000.sql
Pop out
$ | mysqldump --where="true LIMIT 10000" -u user_name -p database_name table_name > table_name_10000.sql |
Change table’s character set to utf8:
mysql> ALTER TABLE table_name CONVERT TO CHARACTER SET utf8;
Pop out
1 | mysql> ALTER TABLE table_name CONVERT TO CHARACTER SET utf8; |
A simple find-and-replace:
mysql> UPDATE table_name SET column_name = REPLACE(column_name, 'find this', 'replace with this');
Pop out
1 | mysql> UPDATE table_name SET column_name = REPLACE(column_name, 'find this', 'replace with this'); |