MySQL部署指南

Mac Mysql -> Windows Mysql

以管理员身份登录mysql

1

mysql -u root -p

选择mysql数据库

1

mysql -u root -p

创建用户并设定密码

1

create user 'xxx'@'localhost' identified by 'yyy'

使操作生效

1

flush privileges

为用户创建数据库

1

create database sms default character set utf8 collate utf8_general_ci;

为用户赋予操作数据库testdb的所有权限

1

grant all privileges on sms.* to xxx@localhost identified  by 'yyy'

允许任何地址远程连接

1

GRANT ALL PRIVILEGES ON *.* TO 'xxx'@'%' IDENTIFIED BY 'yyy' WITH GRANT OPTION;

使操作生效

1

flush privileges

用新用户登录

1

mysql -u test -p

默认字符集导出数据库

1
2

mysqldump -u xxq -p --default-character-set=utf8 sms > sms.sql
mysqldump -t dbname -u root -p > dataonly.sql

转换字符集导出数据库

1

mysqldump -u xxq -p --default-character-set=utf8 --set-charset=latin1 --skip-opt sms>sms.sql

导入数据库

1

mysql -u xxq -p --default-character-set=utf8  sms < sms.sql

mysql重启 

1
2

$mysql_dir/bin/mysqladmin -u root -p shutdown
$mysql_dir/bin/safe_mysqld &

如何查看mysql占用端口，验证mysql是否启动

1
2

netstat -aon | findstr "3306"
telnet mysql 3306

mysql的字符集和校对规则有4个级别的默认设置：服务器级、数据库级、表级和字段级。

1
2
3

show variables like 'character_set_%';
show create table tb_name;
show full columns from tb_name;

如何change服务器级字符集、数据库级、表级和字段级

1
2
3
4
5
6
7
8

修改my.ini  
default-character-set = utf8
character_set_server =  utf8

alter database dbname character set utf8 collate utf8_general_ci;
alter table tb_name convert to character set charset_name;
alter table tb_name modify latin1_text_col text character set utf8;
alter table tb_name change old_col_name new_column varchar(10) character set utf8 collate utf8_general_ci;

低调做人，高调做事