MySQL：Useful Commands

MySQL Useful Commands

Start/Stop/Restart MySQL

On Linux start/stop/restart from the command line:

    /etc/init.d/mysqld start  
    /etc/init.d/mysqld stop  
    /etc/init.d/mysqld restart

Some Linux flavours offer the service command too

    service mysqld start
    service mysqld stop
    service mysqld restart

or

    service mysql start
    service mysql stop
    service mysql restart

On OS X to start/stop/restart MySQL from the command line:

    sudo /usr/local/mysql/support-files/mysql.server start
    sudo /usr/local/mysql/support-files/mysql.server stop
    sudo /usr/local/mysql/support-files/mysql.server restart

Connect

mysql -h localhost -u myname -pMyPassword

Set Password

First set: mysqladmin -u root -password root 
then:
mysql> UPDATE mysql.user SET password=PASSWORD(’新密码’) WHERE User=’root’;
mysql> FLUSH PRIVILEGES;

Show current user

mysql> select user();

Create Database

mysql> create database openfire character set 'utf8';

Show Databases

mysql> show databases;

Use Database

mysql> use database;

Show Tables

mysql> show tables;

Empty database with root user

mysql> DROP DATABASE atomstore;
mysql> CREATE DATABASE atomstore;

Create User

create user 'hy'@'%' identified by 'M@nager13579'

Grant Permission

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'appuser'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Create Readonly User

CREATE USER 'read_test'@'%' IDENTIFIED BY 'readtest';
GRANT SELECT, SHOW VIEW ON *.* TO read_test@'%' IDENTIFIED BY 'readtest';
FLUSH PRIVILEGES;

Show table structure

SHOW [FULL] COLUMNS {FROM | IN} tbl_name [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

Drop database

DROP DATABASE atomstore;

Drop table

DROP TABLE xxxxx;

Create table samples

create table if not exists `fchat`.`user` (
  `userId` int(7) not null auto_increment,
    `username` varchar(64) not null,
    `plainPassword` varchar(32) null,
    `encryptedPassword` varchar(255) null,
    `name` varchar(100) null,
    `email` varchar(100) null,
    `creationDate` datetime not null,
    `modificationDate` datetime not null,
    primary key(`userId`)
);

create table if not exists `fchat`.`tag` (
  `tagId` int(5) not null auto_increment,
  `tagName` varchar(64) not null,
  `userId` int(7) not null,
  primary key(`tagId`),
  foreign key (userId)
  references user(userId)
    on delete cascade
);

Rename Table

RENAME TABLE tbl_name TO new_tbl_name

Create Unique Index

create unique index idx_userName on user (userName);

Show Columns Character

show full columns from ofOffline;

Show Character Set

SHOW VARIABLES LIKE  'char%';

Change Client Character

set names utf8;

Backup database

mysqldump -h 192.168.3.32 -u root -p[root_password] [database_name] > dumpfilename.sql

Backup all the databases

mysqldump -u root -ptmppassword --all-databases > /tmp/all-database.sql

Backup a specific table

mysqldump -u root -ptmppassword sugarcrm accounts_contacts \
     > /tmp/sugarcrm_accounts_contacts.sql

Restore a database

# mysql -u root -ptmppassword

mysql> create database sugarcrm;

# mysql -u root -ptmppassword sugarcrm < /tmp/sugarcrm.sql

# mysql -u root -p[root_password] [database_name] < dumpfilename.sql

Backup a local database and restore to remote server using single command

mysqldump -u root -ptmppassword sugarcrm | mysql \
             -u root -ptmppassword --host=remote-server -C sugarcrm1

根据日期分组统计注册数量
select Date(createDate) date, count(1) count from users where createDate between '2015-07-01 00:00:00' and '2015-07-14 23:59:59' group by Date(createDate);

导出csv文件

mysql -h <host name> -u<user name> -p<password> <database name> -N -B -e "select user.user_id, user.nick_name, user.hs_no, online.online_status, online.create_date from t_user_online_history online, (select user_id, nick_name, hs_no from t_user_base_info where create_date > '2015-09-07 19:00:00') user
where online.user_id = user.user_id" | tee ~/Documents/output.csv

 

创建临时表

CREATE TEMPORARY TABLE temp1 ENGINE=MEMORY 
as (select * from table1)

BUT ENGINE=MEMORY is not supported when table contains BLOB/TEXT columns

Mac

 MariaDB Server can be installed with this command:

brew install mariadb

After installation, start MariaDB Server:

mysql.server start

To auto-start MariaDB Server, use Homebrew's services functionality, which integrates with macOS launchctl:

brew services start mariadb

After MariaDB Server is started, you can log in:

mysql -u root

Export All Databases:

mysqldump -u root -p --all-databases > alldb.sql

Look up the documentation for mysqldump. You may want to use some of the options mentioned in comments:

mysqldump -u root -p --opt --all-databases > alldb.sql
mysqldump -u root -p --all-databases --skip-lock-tables > alldb.sql

Import:

mysql -u root -p < alldb.sql

导入本地TSV文件（忽略第一行）

LOAD DATA local  INFILE '/root/smile_data/han07r001-141231.txt' INTO TABLE HAN07R001TORIHIKIH IGNORE 1 LINES;

查看所有Database的大小：
SELECT table_schema "DB Name",         ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 1) "DB Size in GB"  FROM information_schema.tables  GROUP BY table_schema;