Common MySql Commands

A list of commonly used MySQL commands.
created by on 2014-05-05

Create a database

CREATE DATABASE mydb
  DEFAULT CHARACTER SET utf8
  DEFAULT COLLATE utf8_general_ci;

see: MySQL 5.0 Reference Manual > 10 Globalization > 10.1 Character Set Support > 10.1.5 Configuring the Character Set and Collation for Applications

Create a MySQL User and Grant Permissions

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

Related Links

Restore a dabase from the command line

mysql -h localhost -u <username> -p <database-name> < some-database-backup.sql

Replace a substring

UPDATE core_config_data SET value = REPLACE(value, 'old-value', 'new-value') WHERE value LIKE '%old-value%';

Related Links

Common Errors

ERROR 1449 (HY000): The user specified as a definer (‘user’@‘192.168.200.79’) does not exist

Root cause

The user that created the procedure must’ve been deleted.

Solution

Grant permission for all hosts:

grant all on *.* to 'user'@'%' identified by 'password' with grant option;#
FLUSH PRIVILEGES;
Tags:
Fork allmark on GitHub