MySQL && Mysqldump Useful Command

MySQL && Mysqldump Useful Command

Query MySQL User

select user, host from mysql.user;

Create Database By Use UTF-8

CREATE DATABASE example CHARACTER SET utf8 COLLATE  utf8_general_ci;

CREATE DATABASE example CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Check Database Charset

USE db_name; SELECT @@character_set_database, @@collation_database;

SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'db_name';

mysql import mysqldump export use utf8

mysql -u root -p --default-character-set=utf8 yourDB < yourSQLfile.sql

Config sql_mode

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Enable Vagrant Remote MySQL

 #bind-address       = 127.0.0.1

Create user

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

List Procedure/Function

SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;

Mysqldump --opt -R

-R Include stored routines (procedures and functions) for the dumped databases in the output. 

--opt This option, enabled by default, is shorthand for the combination of --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It gives a fast dump operation and produces a dump file that can be reloaded into a MySQL server quickly.

Reference

1. Linux and Unix mysqldump command