mysql 常用命令

start: service mysql start;
restart: service mysql restart;
stop: service mysql stop;
解压版本：
stop: mysqladmin -uroot shutdown;
start: mysqld

login: /etc/init.d/mysql -uroot -p;
show databases;
use dbname;
show tables;


可能是你的帐号不允许从远程登陆，只能在localhost。这个时候只要在localhost的那台电脑，登入mysql后，更改 "mysql" 数据库里的 "user" 表里的 "host" 项，从"localhost"改称"%"
>mysql -u root -p
>use mysql;
>update user set host = '%' (where user = 'root' limit 1);
>select host, user from user;


编辑 /etc/mysql/my.cnf
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1

将”bind-address = 127.0.0.1“注释
sudo /etc/init.d/mysql restart重启即可远程访问
sudo service mysql restart




[client]
#编码utf－8
default-character-set=utf8

[mysqld]
#
# * Basic Settings
#
#忽略大小写
lower_case_table_names=1
# 编码utf－8
default-character-set=utf8


#远程连接
mysql -h 192.168.0.140 -uroot -p;

备份数据库
mysqldump -h 192.XXXXXX -uroot -p dbname > XXx.sql
{
mysqldump -h 192.168.0.140 -uroot -p iot > iot.sql;
mysqldump -h 192.168.0.140 -uroot -p ktalking > ktalking.sql;
mysqldump -h 192.168.0.140 -uroot -p smarthome > smarthome.sql;
mysqldump -h 192.168.0.140 -uroot -p yuantuostoreco > yuantuostoreco.sql;
}
还原数据库
mysql > use dbname;
mysql > source ~/XX/XX.sql

创建数据库
mysql> create database dbname default charset=utf8;
删除库
mysql> drop database dbname;

查看数据库引擎
show engines;

查看变量
SHOW variables like "XXX%"