mysql tips

To create a database:

To create a user:
GRANT ALL PRIVILEGES ON dbname.* TO 'monty'@'localhost' IDENTIFIED BY 'password';

To reload grant tables:
mysqladmin reload -p

To set a password:
SET PASSWORD = PASSWORD('biscuit'); (for the user you're logged in as)
SET PASSWORD FOR 'monty'@'localhost' = PASSWORD('12345');
SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('biscuit');

Export a database:
mysqldump -u user -p -a > dump.sql

Import the database you exported:
mysql -u user -p < dump.sql

Move databases from one server to another
mysqldump -uroot -plocalrootpassword -a --databases database1 database2 database3 | ssh [email protected] "mysql -uroot -pdestinationrootpassword"

Search and replace text in fields:

UPDATE [table] SET [field] = replace( [field], "search_for", "replace_with" );

Backup live database:
mysqldump -u root -ppassword --all-databases --skip-lock-tables > database-dump.sql

Updated Mar 07, 2024