mysql基础运维

1.创建用户并授权

一般新建数据库都需要新增一个用户，用于程序连接，这类用户只需要insert、update、delete、select权限。

新增一个用户，并授权如下：

（1）grant select,delete,update,insert on *.* to admin@'192.168.0.%' identified by '123456';

flush privileges;

（2）新建用户再授权

create user 'test'@'localhost' identified by 'test';

GRANT ALL ON db1.* TO 'test'@'localhost';

2.创建数据库并指定字符集

create database test DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

3.显示数据库

show databases;

show databases like '%test%';

显示当前数据库

select database();

4.使用数据库

use test;

显示当前用户

select user();

5.删除数据库

drop database test;

6.删除用户(系统多余账号)

（1）drop user "user"@主机域（主机名大写删不掉）

（2）delete from mysql.user where user = 'root' and host = 'test';

flush privileges;

7.查看数据库版本

select version();

8.查看当前时间

select now();

9.查看用户列表

select user,host from mysql.user;

10.查看用户权限

show grants for test@localhost;

11.授权主机远程连接数据库

 

12.企业环境授权

（1）对于web连接用户授权尽量采用最小化原则，很多开源软件 还需要create,drop等比较危险的权限

（2）生成数据库表后，要收回create/drop授权

revoke insert on *.* FROM 'test'@'localhost';

生产环境针对主库（写为主读为辅）用户的授权：

后面再说

13.建表

create table student(

id int(4) not null,

name char(20) not null,

age tinyint(2) not null default '0',

dept varchar(16) default null

)ENGINE=InnoDB DEFAULT CHARSET=uft8;

14.增删表字段

alter table test add a varchar(20);

15.更改表明

rename table test to test1;

16mysql插入中文不乱码的5种方法

（1）登录mysql，先做"set names latin1"，然后再执行语句或语句文件

（2）在sql文件中指定set names latin1；然后登录mysql，通过如下命令执行

mysql>source test.sql

（3）在sql文件中指定set names latin1;然后通过mysql命令导入数据

mysql -uroot -poldboy123 oldboy<test.sql

mysql -uroot -poldboy123 oldboy -e "set names latin1;select * from oldboy.test";

（4）通过指定mysql命令的字符集参数实现--default-character-set=latin1

cat test.sql

INSERT INTO student VALUES (9,'张三');

mysql -uroot -poldboy123 --default-character-set=latin1 oldboy<test.sql

（5）在配置文件里设置客户端及服务端相关参数

更改my.cnf客户端模块的参数，可以实现set names latin1的效果，并且永久生效

[client]

default-character-set=latin1

提示:无需重启服务，退出重新登录就生效。相当于set names latin1;

更改my.cnf参数

[mysqld]

default-character-set=latin1适合5.1及以下版本

character-set-server=latin1适合5.5

不乱码的思想：linux，客户端，服务端，库，表，程序保证字符集统一

不乱码的思想：建议中英文环境选择utf8

建库：create database oldboy DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci

建表：一般情况下，建库是指定字符集，表不用管

create table student

(id int(4) not null auto_increment,

name char(20) not null,

primary key(id )

)ENGINE= InnoDB AUTO_INCREMENT=10 DEFAULT CHARATERSET=UTF8;

查看校对规则show character set;

查看状态

show variables;

show global status;

set global key_buffer_size=

show variables liek 'key_buffer%'

作为作业总结

mysqldump

mysqlbinlog

mysql

mysqladmin

 

客户端字符集

character_set_client

character_set_connection

character_set_results

set names就该更改了上面三个变量的值

 

确保mysql数据库插入数据不乱吗解决方案

统一mysql数据库客户及服务端字符集

1.通常，mysql数据库的下面几个字符集（客户端和服务端）统一成一个字符集，才能确保插入的中文数据可以正确输出。即mysql>show variables like 'character_set%' 结果中的字符集设置尽量统一。当然linux系统的字符集也要尽可能和数据库字符集统一。

2、show variables like 'character_set%';命令结果输出如下

variable_name |value

1character_set_client |latin1 #客户端字符集，set names latin1;

2character_set_connection |latin1 #连接字符集，set names latin1;

3character_set_database |latin1 #数据库字符集，配置文件制定或建库建表制定。

4character_set_results |latin1 #返回结果字符集，set names latin1;

5character_set_server |latin1 #服务器字符集，配置文件制定或建库建表制定。

其中，1、2、4 3个参数默认情况采用linux系统字符集设置，人工登录数据库执行“set names latin1“以及mysql制定字符集登录操作，都是改变了mysql客户端的client、connection、results3个参数的字符集为latin1，从而解决了插入中文乱码的问题，这个操作也可以通过更改my.cnf实现mysql客户端的字符集，配置方法如下。

[client]

default-character-set=latin1

提示：无需重启服务，退出重新登录就生效。此参数相当于，登录后执行set names latin1;

[mysqld]

character-set=latin1

提示：需要重启服务

更改的参数是character_set_database和character_set_server

说明：

default-character-set=gbk <=适合5.1及以前的版本

character-set-server=gbk <=适合5.5

结论：

不乱码的思想：建议中英文环境选择utf8

1、linux系统

#cat /etc/sysconfig/i18n

LANG="zh_CN.utf8"

提示：linux客户端也要更改字符串，例如：SecureCRT->

2、mysql客户端

临时：set names utf8;

永久：更改my.cnf客户端模块的参数，可以实现set names utf8的效果，并且永久生效。

[client]

default-character-set=utf8

提示：无需重启服务，退出重新登录就生效，相当于set names utf8;

3.mysql数据库的服务端

更改my.cnf

[mysqld]

default-character-set=utf8 适合5.1及以前的版本

character-set-server=utf8 适合5.5

4、mysql数据库中建库建表

建库：create database oldboy DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci

建表：一般情况下，建库是指定字符集，表不用管

create table student

(id int(4) not null auto_increment,

name char(20) not null,

primary key(id )

)ENGINE= InnoDB AUTO_INCREMENT=10 DEFAULT CHARATERSET=UTF8;

 

更改生产线上Mysql数据字符集的方案

更改数据库/表的字符集

数据字符集修改步骤

对于已有的数据库想修改字符集不能直接通过"alter database character set *"或“alter tablename character set *"这两个命令都没哟更新已有记录的字符集，而只是对新创建的表或者记录生效。

已经有记录的字符的调整，必须先将数据导出，讲过修改字符集后重新导入后才可完成。

1.导出表结构

msyqldump -uroot -p --default-character-set=latin1 -d dbname>alltable.sql

--default-character-set=gbk表示以GBK字符集进行连接 -d 只导表结构

 

2.编辑alltable.sql将latin1改成GBK

3.确保数据库不再更新,，导出所有数据

mysqldump -uroot -p --quick --no-create-info --extended-insert --default-charater-set=latin1 dbname>alldata.sql

参数说明：

--quick:用于转储大的表，强制mysqldump从服务器一次一行的检索数据而不是检索所有行，并输出前CACHE到内存中

--no-create-info：不创建CREATE TABLE语句

--extended-insert：使用包括几个VALUES列表的多行INSERT语法，这样文件更小，IO也小，导入数据时会非常快

--default-charater-set=latin1 #按照原有字符集导出数据，这样导出的文件中，所有中文都是可见的，不会保存成乱码

4.打开alldata.sql将set names latin1修改成 set names GBK;(或者修改系统的服务端和客户端)

5.建库

create database dbname default charset gbk;

6.创建表，执行alltable.sql

mysql -uroot -p dbname<alltable.sql

7.导入数据

mysql -uroot -p dbname<alldata.sql

 

总结：Latin1->utf8

1、建库及建表的语句导出，sed批量修改为utf8

2、导出所有数据

3、修改mysql服务端和客户端编码为utf8

4、删除原有的库表及数据

5、导入建库及建表的语句

6、导入mysql的所有数据