MySQL基本用法

最新推荐文章于 2024-08-14 22:03:11 发布

ppc_z

最新推荐文章于 2024-08-14 22:03:11 发布

阅读量475

点赞数

MySQL的root密码忘记和用户权限问题

1 MySQL的root修改密码和忘记密码的问题

1.1 MySQL的登录问题：

可以使用mysql -uroot p123来登录MySQL，但是这样不好，会使MySQL的密码在历史记录中出现，我们最好使用

mysql -uroot -p

Password:

专业就会防止MySQL密码记录在history中

我们也可以在命令行使用下面的命令：

HISTCONTROL=ignorespace #不记录敏感的命令，这样会临时生效，你可以在/etc/profile中进行配置，让其永久生效

但是在敏感的命令前加一个空格执行，那么history就不会记录该条命令，

1.2 MySQL的服务进程启动和停止问题

如果在单例模式下，

启动：

/etc/init.d/mysql start

使用/etc/init.d/mysqld stop 就可以关闭

或者使用root的用户名和密码进行关闭MySQL服务

mysqladmin -uroot -p123 shutdown

前两种方法推荐使用，如果前两种方法无法使用，使用

pkill mysql 来结束MySQL的服务进程

多实例模式下，

启动：

/data/3306/mysql start

/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &

停止：

/data/3306/mysql stop #等学到了shell编程，自己去看mysql脚本

${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown

1.3单例情况下，MySQL的root密码忘记情况

在这之前先说一下，如何修改用户密码的情况，下面的MySQL的命令都是已经在环境变量配置好的

修改用户密码(在知道用户密码的情况下)：旧的密码为123，修改新的密码为456

A) mysqladmin -uroot -p123 password '456'

B) 进入MySQL中，使用SQL来修改用户的密码：

首先查看一下root用户的密码，看一下，发现是加密的。

mysql> select user,host,password from mysql.user;

+------+-----------+-------------------------------------------+

| user | host | password |

+------+-----------+-------------------------------------------+

| root | localhost | *531E182E2F72080AB0740FE2F2D689DBE0146E04 |

| root | 127.0.0.1 | |

+------+-----------+-------------------------------------------+

我们使用SQL修改如下：

update mysql.user set passwod='123' where user='root' and host='localhost'

我们在查看一下新的密码：

mysql> select user,host,password from mysql.user;

+------+-----------+----------+

| user | host | password |

+------+-----------+----------+

| root | localhost | 123 |

| root | 127.0.0.1 | |

+------+-----------+----------+

2 rows in set (0.00 sec)

发现密码没有加密，我们执行： flush privileges;来刷新权限，但是发现在客户端使用：mysql -uroot -p123无法登录

原因：客户端获取密码以后，先用算法加密，在与数据库中的密码进行比对，所以数据库里面的的密码123是加密以后的密码，所以我们需要对明文的‘123’进行加密，如何设置呢?

其实只有把上面的修改密码的SQL语句修改一下即可：

mysql> update mysql.user set password=password("123") where user='root' and host='localhost';

在查询一下用户密码：

mysql> select user,host,password from mysql.user;

+------+-----------+-------------------------------------------+

| user | host | password |

+------+-----------+-------------------------------------------+

| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

| root | 127.0.0.1 | |

+------+-----------+-------------------------------------------+

2 rows in set (0.00 sec)

发现密码已经加密，在执行flush privileges;刷新权限，就可以登录了

多实例的情况下，在使用MySQL命令授权的时候，需要指定一下多实例的sock文件

下面是具体的命令：

1.4 学会使用MySQL的帮助

登录MySQL，在MySQL中，如果对命令什么的不清楚，可以使用 COMMAND HELP

例如，如果对create命令不清楚，可以使用

mysql> help create

Many help items for your request exist.

To make a more specific request, please type 'help <item>',

where <item> is one of the following

topics:

CREATE DATABASE

CREATE EVENT

CREATE FUNCTION

CREATE FUNCTION UDF

CREATE INDEX

CREATE LOGFILE GROUP

CREATE PROCEDURE

CREATE SERVER

CREATE TABLE

CREATE TABLESPACE

CREATE TRIGGER

CREATE USER

CREATE VIEW

SHOW

SHOW CREATE DATABASE

SHOW CREATE EVENT

SHOW CREATE FUNCTION

SHOW CREATE PROCEDURE

SHOW CREATE TABLE

SPATIAL

在对你需要的create xxx help,例如你想知道如果create database，你可以使用

Help CREATE DATABASE,you will find hwo to use this command.

mysql> help CREATE DATABASE

Name: 'CREATE DATABASE'

Description:

Syntax:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

[create_specification] ...

create_specification:

[DEFAULT] CHARACTER SET [=] charset_name

| [DEFAULT] COLLATE [=] collation_name

CREATE DATABASE creates a database with the given name. To use this

statement, you need the CREATE privilege for the database. CREATE

SCHEMA is a synonym for CREATE DATABASE.

URL: http://dev.mysql.com/doc/refman/5.1/en/create-database.html

1.5 root密码忘记

单实例的情况下：

如果MySQL的root密码忘记了，首先我们需要停止MySQL的服务.关于如何停止MySQL服务进程，在什么已经介绍了，现在就不重复叙述了。

然后使用忽略用户授权的方法来启动MySQL的服务进程，具体的命令如下：

mysqld_safe --skip-grant-tables --user=mysql

但是在二进制解压安装的机器上会出现问题，所以我们在多实例的机器上进行测试(多实例MySQL是编译安装的，在实际企业中，是先使用编译安装，然后把编译安装的做成rpm包，然后在批量的安装)

如果在多实例的机器上测试，那么就需要增加一个配置文件

mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-table

然后你使用

ss -lntup|grep 330，你将会发现3306的端口已经启动了，你在用登录的命令就可以登录MySQL客户端了。

mysql -S /data/3306/mysql.sock

然后进去修改root密码，使用什么说过的update语句

update mysql.user set password=password("123") where user='root' and host='localhost';

然后刷新权限（ flush priviliges;）。此时密码以及修改成功。我们需要

退出客户端，使用kill 命令来杀死MySQL进程，在用正常的方式来启动MySQL服务进程(/data/3306/mysql start)，使用新密码即可重新登录( mysql -uroot -p123 -S /data/3306/mysql.sock )

1.6 MySQL的默认字符集以及如果设置数据库的字符集

如果在编译的时候设置了MySQL的字符集，那么在创建数据库和数据表时不特定指定MySQL的字符集，那么使用的就是默认的字符集来创建数据库和数据表

我们用下面的命令来查看一个数据库或者数据表使用的是什么样的字符集

mysql> show create database 3306d\G

*************************** 1. row ***************************

Database: 3306d

Create Database: CREATE DATABASE `3306d` /*!40100 DEFAULT CHARACTER SET utf8 */

1 row in set (0.06 sec)

可以看的出来，使用的是utf8的字符集，是因为在编译的时候指定的，不然编译的时候不指定，那么默认是拉丁(latin1)

那么我们如何创建指定字符集的数据库或者数据表呢？

create database yghdb1 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; #指定数据的格式为gbk

#查看是否为gbk

mysql> show create database yghdb1\G

*************************** 1. row ***************************

Database: yghdb1

Create Database: CREATE DATABASE `yghdb1` /*!40100 DEFAULT CHARACTER SET gbk */

create database yghdb2 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; #指定编码为utf8

1.7 创建用户和授权

在数据库中，为是数据安全，我们一般对不使用root用户来登录数据库，只使用普通用户去连接他可以管理的数据库，因此我们创建普通用户和给其授权。

第一种方法，先创建，在授权

创建用户使用下面的命令:

create user 'ygh1'@'localhost' identified by '123';

用户名和主机密码

上面的命令仅仅是创建了一个用户，该用户不具备任何权限，只能登录数据库，无法进入和查询没有权限的数据库。

下面给刚才创建的用户进行授权：

GRANT ALL ON yghdb1.* TO 'ygh1'@'localhost';

通用的写法

GRANT privileges ON daname.tablename TO 'username'@'host';

查看具体用户的权限使用下面的命令：

mysql> show grants for 'ygh1'@'localhost';

+-------------------------------------------------------------------------------------------------------------+

| Grants for ygh1@localhost |

+-------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'ygh1'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |

| GRANT ALL PRIVILEGES ON `yghdb1`.* TO 'ygh1'@'localhost' |

+-------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

第二种方式，授权并创建用户

GRANT ALL PRIVILEGES ON yghdb2.* TO 'ygh2'@'localhost' IDENTIFIED BY '123';

通用的写法：

GRANT ALL PRIVILEGES ON dbName.tableName TO 'username'@'host' IDENTIFIED BY 'password';

查看ygh2的用户权限：

mysql> SHOW GRANTS FOR ygh2@localhost;

+-------------------------------------------------------------------------------------------------------------+

| Grants for ygh2@localhost |

+-------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'ygh2'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |

| GRANT ALL PRIVILEGES ON `yghdb2`.* TO 'ygh2'@'localhost' |

+-------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

查看所有用户的对数据库和表的权限：

select *from mysql.user\G;

\G是为了显示好看一点。

1.8 如果移除用户的某些权限

在数据库中，我们为了安全起见，给用户的权限应该满足用户的需求即可，不能给太大的权限。

那我们如果去撤销用某些特权,例如撤销用户ygh2的insert权限。

首先查看一下ygh2对哪些数据库有哪些权限。

GRANT USAGE ON *.* TO 'ygh2'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |

#上面的表示ygh2只是一个普通用户，对*.*(所有的数据库和表没有任何权限)，

USAGE 表示没有任何权限，只能登录。

| GRANT ALL PRIVILEGES ON `yghdb2`.* TO 'ygh2'@'localhost'

#上面的说明给ygh2对yghdb2的权限

使用下面的命令撤销ygh2对yghdb2的insert权限：

REVOKE INSERT ON yghdb2.* FROM 'ygh2'@'localhost';

通用的方法写法：

REVOKE privileges ON dbname.tableName FROM 'username'@host;

我们在查看一下ygh2对yghdb2的权限

mysql> show grants for 'ygh2'@'localhost';

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Grants for ygh2@localhost |

| GRANT USAGE ON *.* TO 'ygh2'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |

| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `yghdb2`.* TO 'ygh2'@'localhost' |

2 rows in set (0.00 sec)

由此我们可以得出用户在数据库中权限有：

1 SELECT

2 INSERT

3 UPDATE

4 DELETE

5 CREATE

6 DROP

7 REFERENCES

8 INDEX

9 ALTER

10 CREATE TEMPORARY TABLES

11 LOCK TABLES

12 EXECUTE

13 CREATE VIEW

14 SHOW VIEW

15 CREATE ROUTINE

16 ALTER ROUTINE

17 EVENT

18 TRIG

对于一开始网站的测试和架构阶段，我们可以给较大的权限，后来网站正常运行，我们值给网站基本的权限(SELECT,INSERT,UPDATA,DELETE).

注：在Linux命令行也可以不进行MySQL而执行MySQL里面的命令

mysql -uroot -p123 -S /data/3306/mysql.sock -e 'show grants for 'ygh2'@'localhost';'

通用写法：4

mysql -uroot -p123 -S /data/3306/mysql.sock -e 'MYSQL COMMAND'

1.9 如果设置MySQL的用户的远程登录

设置MySQL的用户还可以这么进行设置，

a)使用根据IP来设置

GRANT ALL ON *.* TO test@'192.168.160.%' identified by '123';

通用的写法：

GRANT PRIVILEGES ON dbName.tableName TO username@'192.168.160.%' IDENTIFIED BY 'password';

你在另一台机器上，使用下面的命令来进行登录

mysql -utest -p123 -h 192.168.160.129 -P 3306

指定IP 指定端口

b)根据网段来设置

GRANT ALL ON *.* TO test2@'192.168.160.255/255.255.255.0' identified by '123';

这个我没有测试成功，不知道为什么，但是上面的IP的是可以的。

2 MySQL的常用操作

2.1主键和索引

首先创建一张表

create table student( t_id varchar(32), t_name varchar(60), t_age int, t_dept varchar(23));

A):设置该表的t_id 为主(PRI)

如果在创建表的时候，可以进行这么设置

create table student( t_id varchar(32) primary key,

如果是表已经创建好了，那么可以使用下面的SQL来执行

添加已有的列作为主键索引：

alter table student add t_id varchar(32) primary key;

添加一个新的列作为主键索引

alter table teacher add t_id varchar(32) primary key;

删除一个已经存在的主键索引：

alter table teacher drop primary key;

添加一个可重复的普通索引:

alter table student add index index_dept(t_dept);

通用的写法

ALTER TABLE stableName ADD INDEX indexName(columName);

添加一个普通索引，要求把这列的前三个字符作为普通索引

alter table student add index index_name(t_name(3));

创建联合索引，例如让dept和name变为联合索引

alter table student add index index_name_dept(t_name(3),t_dept);

联合索引的前缀生效特性:在(abc)作为联合索引的时候，只有abc,ab,a才会走索引。

查看一个表中的索引

desc tableName;MUL表示普通索引。

+-----+

| Key |

+-----+

| PRI |

| MUL |

| |

| MUL |

+-----+

还有一种比较全面的方法来查看索引

show index from student\G;

mysql> show index from student\G;

*************************** 1. row ***************************

Table: student

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: t_id

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

*************************** 2. row ***************************

Table: student

Non_unique: 1

Key_name: index_dept

Seq_in_index: 1

Column_name: t_dept

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

*************************** 3. row ***************************

Table: student

Non_unique: 1

Key_name: index_name

Seq_in_index: 1

Column_name: t_name

Collation: A

Cardinality: 0

Sub_part: 3

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

*************************** 4. row ***************************

Table: student

Non_unique: 1

Key_name: index_name_dept

Seq_in_index: 1

Column_name: t_name

Collation: A

Cardinality: 0

Sub_part: 3

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

*************************** 5. row ***************************

Table: student

Non_unique: 1

Key_name: index_name_dept

Seq_in_index: 2

Column_name: t_dept

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

5 rows in set (0.00 sec)

删除普通索引：

alter table student drop index index_name;

通用的写法:

ALTER TABLE tableName drop index indexName;

创建唯一索引(UNI)：

alter table student drop index index_name;

2.2 数据库的备份

多实例每个命令指定sock的即可

MySQL的数据的逻辑备份使用的mysqldump命令

用法：

2.2.1只备份数据表，不备份数据库

也就是没有create database和user database 的语句

mysqldump -u username -p password dbName > name.sql

恢复的语句：你首先需要进行数据库，然后创建好数据库，才能使用下面的命令

mysql -u username -p password dbName < name.sql

2.2.2 备份数据库

加上-B的参数，这样在备份的SQL脚本中，会有create database和use

Database的SQL语句

mysqldump -u username -p password -B dbName > name.sql

恢复语句：

mysql -u username -p password < name.sql

2.2.3备份并且压缩的语句

mysqldump -u username -p password -B dbName|gzip > name.sql.gz

直接把备份的SQL脚本压缩

如果使用gzip压缩的SQL脚本，不能直接使用下面的语句进恢复

mysql -u username -p password < name.sql.gz

先使用gzip -d filename.sql;进行解压，-d表示会删除原来的压缩文件

所有，对解压后的文件要小心的保存

mysql -u username -p password < name.sql

即可完成备份数据库

2.2.4 备份多个数据库

mysqldump -u username -p password -B dbName1 dbName1|gzip > all.sql.gz

恢复的方式安装什么的来

MySQL的分库备份的命令：

备份的脚本：

basedir=/tmp/mysqlback

for dbname in $(mysql -S /data/3306/mysql.sock -uroot -p123 -e "show databases;"|grep -Eiv "informa|perfo|database|mysql");

echo $dbname;

mysqldump -uroot -p123 -B -S /data/3306/mysql.sock ${dbname}|gzip > ${basedir}/${dbname}.gz

done

注：在别名里面的alias grep=”egrep --color”在脚本里面是不生效的,只限制在命令行里面使用，在脚本里面还是要写成grep -E 或者egrep

2.2.5 备份单个数据表

Mysqldump -u username -p password dbNameame tbName1(tbName2) > name.sql

例: mysqldump -uroot -p123 -S /data/3306/mysql.sock --compact yghdb1 student

--compact:在备份数据库时把无关的注释都去掉，只留下SQL语句，只限于测试环境使用，应用中应加上完整的备份

如果只想备份表结构而不备份数据，那么使用 -d 参数

[root@nfs_clinet_2 ~]# mysqldump -uroot -p123 -S /data/3306/mysql.sock --compact -d yghdb1 student

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `student` (

`t_id` varchar(32) NOT NULL,

`t_name` varchar(60) DEFAULT NULL,

`t_age` int(11) DEFAULT NULL,

`t_dept` varchar(23) DEFAULT NULL,

PRIMARY KEY (`t_id`),

UNIQUE KEY `uni_index_age` (`t_age`),

KEY `index_dept` (`t_dept`),

KEY `index_name_dept` (`t_name`(3),`t_dept`)

) ENGINE=InnoDB DEFAULT CHARSET=gbk;

/*!40101 SET character_set_client = @saved_cs_client */;

如果只想备份表中的数据，，那么把-d换成-t

[root@nfs_clinet_2 ~]# mysqldump -uroot -p123 -S /data/3306/mysql.sock --compact -t yghdb1 student

INSERT INTO `student` VALUES ('1101','ygh',30,'stu'),('12编号','袁国浩',23,'学士');

2.2.6如果想备份MySQL里面的所有的库和表，

那么使用 -A -B 和 --events

mysqldump -uroot -p123 -S /data/3306/mysql.sock -A -B --event|gzip >/tmp/mysqlback/all.gz

2.2.7 mysqldump的其他参数

用来切割bin-log文件。当我们做一次备份的时候，在到下一次备份之前，数据库数据丢失如何找回。

这就需要使用最新一次的备份数据+bin-log来找回数据库的数据。

因此，在我们备份的时候，需要知道此时的bin-log点是很重要的，不然在下次备份前，数据库数据丢失，那就不好恢复了。

因此我们使用

mysqldump -uroot -p123 -S /data/3306/mysql.sock -A -B -F --event|gzip >/tmp/mysqlback/all.gz

来备份数据库是一个好的做发

还有一个比这个更好的做法，就是使用--master-data=1这个参数，

这个参数的作业是在SQL的备份脚本中，指定当前备份对应的bin-log的内容，并且在恢复时自动恢复。是一个很好的备份数据的方法。

--master-data=2,也是可以的，这样在SQL脚本中的bin-log将会被注释掉，显得的很灵活，运维可以根据自己的需要来确定是否要执行bin-log 脚本

[root@nfs_clinet_2 ~]# mysqldump -uroot -p123 -S /data/3306/mysql.sock --master-data=1 --compact -B yghdb1

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000019', MASTER_LOG_POS=107; #指定bin-log的位置

-x --lock-all-tables:锁表

-l lock-tables(lock all tables for read):只读锁表

--single-transaction:适合innodb引擎的事务备份，在innodb引擎中，通常使用innodb引擎来来保持备份的一致性，实际工作中，要求修改隔离级别为(REPEATBLE READ)(可重复读)来保证本次备份时，不会备份其他已提交的事务

2.2.8 普通企业的数据备份方法:

myisam引擎

myslqdump -uroot -p123 -A -B --event -F --master-data=2 -x|gzip > backup.sql

Innodb引擎

myslqdump -uroot -p123 -A -B --event -F --master-data=2 --single-transaction|gzip > backup.sql

2.2.9MySQL企业的专业DBA的备份方法

2.2.10 如何把一个目录下面的xxx.gz脚本恢复的数据库

例如，在/tmp/mysqlback有如下的备份文件，如何把他们还原到数据库

[root@nfs_clinet_2 mysqlback]# ll

总用量 16

-rw-r--r-- 1 root root 510 3月 5 16:12 3306d.gz

-rw-r--r-- 1 root root 509 3月 5 16:12 test.gz

-rw-r--r-- 1 root root 1014 3月 5 16:12 yghdb1.gz

-rw-r--r-- 1 root root 780 3月 5 16:12 yghdb2.gz

A)首先解压全部的文件使用如下的命令

gzip -d *.gz

然后执行下面的脚本

basedir=/tmp/mysqlback/

cd $basedir

for dbname in `ls * `;

mysql -uroot -p123 -S /data/3306/mysql.sock < $dbname;

done

2.2.11 使用mysqlbinlog 来恢复数据库

按照数据库来拆分bin-log

mysqlbinlog -d yghdb1 mysql-bin.000020

通用写法:

Mysqlbinlog -d dbName binlogFile

指定位置来恢复数据库

mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456
| mysql -u root -pmypwd
mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456
| mysql -u root -pmypwd

指定时间恢复：

mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/bin.123456
| mysql -u root -pmypwd

2.3 MySQL的乱码问题

MySQL的乱码问题主要是由于客户端和服务器的字符集编码不一致所造成的。

使用

show create database yghdb1(dbName)\G; #查看数据库的字符集编码

show create table teacher(tableName)\G; #查看数据库表的字符集编码

在创建数据库的时候可以指定数据库或者数据表的字符集，例如下面的语句：

create database yghdb1 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; #指定数据的字符编码gbk

当然数据的默认编码格式可以在编译中指定，也可以在my.cnf中进行配置，可以参考下面的文档。

使用”show character set;”可以查看MySQL支持的字符集编码

如何想让数据库不乱码，可以使用 set names gbk; #指定数据库客户端的编码，但这只是临时生效

这样让MySQL的客户端和服务器字符编码统一，就不会乱码了。

在配置数据库配置文件中指定，在

5.1及以前的版本，在

[client] #MySQL客户端编码

default-character-set=utf8

[mysqld] #MySQL的服务器编码

default-character-set=utf8

在5.5的配置文件中，下面的两种方式人选其一即可

[client] #MySQL客户端编码

default-character-set=utf8

character-set-server=utf8

[mysqld] #MySQL的服务器编码

default-character-set=utf8

character-set-server=utf8

或者在使用source的脚本导入mysql脚本时，加上set names utf8.#指定客户端的字符集。但是服务端的字符集还有和SQL脚本统一。

查看MySQL的字符编码情况：

mysql> show variables like '%character_set%';

+--------------------------+-------------------------------------------+

| Variable_name | Value |

+--------------------------+-------------------------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | /application/mysql-5.5.32/share/charsets/ |

+--------------------------+-------------------------------------------+

8 rows in set (0.00 sec)

保证上面的统一就不会乱码了，而且MySQL客户端如果不指定，默认和系统的字符集编码一样。

如果你的Linux不支持中文，那么你可以去修改Linux的字符集编码

编辑vim /etc/sysconfig/i18n

LANG="zh_CN.UTF-8"

推荐使用utf-8的编码

3 MySQL的优化

3.1 使用explain来查看SQL语句的执行

现在有一张表的数据和描述如

mysql> select *from teacher;

+--------+------+------+

| t_name | age | t_id |

+--------+------+------+

| ygh1 | 12 | 1101 |

| ygh2 | 13 | 1102 |

| ygh3 | 14 | 1103 |

| ygh4 | 15 | 1104 |

| ygh5 | 16 | 1105 |

+--------+------+------+

5 rows in set (0.00 sec)

mysql> desc teacher;

+--------+-------------+------+-----+---------+-------+

+--------+-------------+------+-----+---------+-------+

| age | int(11) | YES | | NULL | |

| t_id | varchar(32) | NO | MUL | NULL | |

+--------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

分别使用索引搜索和无索引搜索

mysql> explain select *from teacher where t_name='ygh5'\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: teacher

type: ALL

possible_keys: NULL #无可用索引

key: NULL

key_len: NULL

ref: NULL

rows: 5 #搜索了五条记录

Extra: Using where

1 row in set (0.00 sec)

ERROR:

No query specified

mysql> explain select *from teacher where t_id='1105'\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: teacher

type: ref

possible_keys: index_id #可用索引

key: index_id

key_len: 66

ref: const

rows: 1 #搜索了一条记录

Extra: Using where

1 row in set (0.02 sec)

3.2 登录时加上-U来防止用户误操作

正常登录进入MySQL，如果执行这条语句是会执行成功的。

update teacher set t_name='ygh';

但是如果我们在登录时添加一个参数

mysql -uroot -p123 -S /data/3306/mysql.sock -U

那么在执行update teacher set t_name='ygh';就会有下面的报错:

mysql> update teacher set t_name='ygh';

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

可用使用别名来防止误操作

3.3 打开MySQL的日志监控

在MySQL的配置文件中，我们可以加上一条配置

log-bin = /data/3306/mysql-bin

这样所有的MySQL的修改或者插入操作都会记录在这个文件里面，但是

这个文件是一个日志文件，使用vim无法查看，只能使用

Mysqlbinlog 来查看

之所以要加上日志文件，是为了数据库被误操作或者黑客攻击以后可以进行恢复。

3.4 生产中，如何修改已经正在使用的数据库字符集

例如latin1 --> utf8

A)首先停止数据库的服务

B)建库已经建表语句的导出，修改latin1为utf8

C)导出MySQL的所有数据

D)修改MySQL的服务端和客户端编码为utf8

E)删除原来的所有表和数据

F)导入新的建库语句

G)导游已备份的所有数据。

在企业应用中，应提前指定好编码。

3.5 MySQL的一些常用的查看命令

-e 这个参数，可以在命令行直接执行MySQL的命令，方便shell编程和数据的导入导出

show processlist;#查看MySQL的所有的连接数

[root@nfs_clinet_2 mysqlback]# mysql -uroot -p123 -S /data/3306/mysql.sock -e "show processlist";

+----+------+-----------+------+---------+------+-------+------------------+

+----+------+-----------+------+---------+------+-------+------------------+

+----+------+-----------+------+---------+------+-------+------------------+

show variables;#查看MySQL的参数，只要在配置文件中配置，基本上都能查到，也可以检查配置文件里面配置的内容是否生效

mysql -uroot -p123 -S /data/3306/mysql.sock -e "show variables";

例：

[root@nfs_clinet_2 mysqlback]# mysql -uroot -p123 -S /data/3306/mysql.sock -e "show variables;"|grep "log_bin"

log_bin ON

log_bin_trust_function_creators OFF

sql_log_bin ON

show global status:查看MySQL的全局状态,我们可以把MySQL的状态参数提取出来，然后做成一张状态图，对MySQL的性能进行时时的监测

mysql -uroot -p123 -S /data/3306/mysql.sock -e "show global status;"

例:

[root@nfs_clinet_2 mysqlback]# mysql -uroot -p123 -S /data/3306/mysql.sock -e "show global status;"|grep select

Com_insert_select 0

Com_replace_select 0

Com_select 456

#查看一共进行了多少此的select

show variables #查看MySQL的参数

mysql -uroot -p123 -S /data/3306/mysql.sock -e "show variables;"

关于修改启动中MySQL的参数问题：

我们知道，MySQL的参数写在配置文件中，如果我们修改配置文件，那需要重启MySQL才能生效，那么如果在不重启MySQL的情况下，修改MySQL一些参数的大小

例如在/data/3306/my.cnf中key_buffer_size = 16M，但是我现在需要调整他为32M，而且MySQL正在运行，如何调整：

方法：

首先查看全局参数有没有这个参数

mysql -uroot -p123 -S /data/3306/mysql.sock -e "show variables;"|grep "key_buffer_size"

key_buffer_size 16777216

#发现有，我们可以登录MySQL进行修改

mysql> show variables like "%key_buffer%";

+-----------------+----------+

| Variable_name | Value |

+-----------------+----------+

| key_buffer_size | 16777216 |

+-----------------+----------+

1 row in set (0.00 sec)

其实在MySQL中也可以进行查询

mysql> set global key_buffer_size=1024*1024*32; #设置新的值

Query OK, 0 rows affected (0.02 sec)

mysql> show variables like "%key_buffer%";

+-----------------+----------+

| Variable_name | Value |

+-----------------+----------+

| key_buffer_size | 33554432 |

+-----------------+----------+

1 row in set (0.00 sec)

#结果竟然被改变了，但是如果重新启动MySQL，就会失效，因为MySQL就会配置文件里面读取。

所有修改正在启动的MySQL的参数的方法：首先登录MySQL进行修改，然后在配置文件修改，类似修改SELinux和hostname

4 MySQL的主从复制

原理图：

主从复制的操作步骤：

A):准备工作，先准备两个虚拟主机或者两个多实例的MySQL，一个作为主库，一个作为从库。我这里使用MySQL的多实例，以3306作为MySQL的主库，以3307作为从库

B)先打开主库的bin-log的日志功能log-bin = /data/3306/mysql-bin

C)分别查看两个实例的my.cnf，确保两个多实例的server-id的不同

[root@nfs_clinet_2 3306]# grep server-id /data/3306/my.cnf /data/3307/my.cnf

/data/3306/my.cnf:server-id = 1

/data/3307/my.cnf:server-id = 3

D)在主库创建从库的访问用户，并给予权限

grant replication slave on *.* to 'rep'@'192.168.204.%' identified by '123';

E)给主库备份数据

mysqldump -uroot -p123 -S /data/3306/mysql.sock -A -B --events --master-data=1 -x > /tmp/mysqlback/all.sql

less /tmp/mysqlback/all.sql 查找CHANGE ，你会发现MASTER_LOG_POS=756;

和在3306数据库使用show master status的值是一样的。

mysql> show master status;

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000020 | 756 | | |

+------------------+----------+--------------+------------------+

F)把主库备份好的数据导入到从库

mysql -uroot -p123 -S /data/3307/mysql.sock < /tmp/mysqlback/all.sql

G)登录从库的3307，执行change master操作

mysql> change master to

-> master_host='192.168.204.130',#主服务器IP

-> master_user='rep', #上面主库配置的用户

-> master_password='123',#用户的密码

-> master_log_file='mysql-bin.000020', #备份主库时bin-log文件

-> master_log_pos=756; #位置，从上面的show master status得出

Query OK, 0 rows affected (0.33 sec)

H)登录3307的MySQL实例，开启slave主从复制

start slave;

然后执行 show slave status\G;如果看到下面的信息IO和SQL这两个线程已经启动了。

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

I)检查和测试，在主库创建一个数据库，在从库使用show database看主库和从库是否一致

如果从库还想级联从库，需要打开从库的bin-log和log-server-update

企业快速的MySQL主从复制方案：

MySQL的错误代码对于的问题：

My.cnf中和MySQL主从同步的参数介绍

如何保证从库的只读：在从库的配置文件的[mysqld]模块下面加上read-only

但是read-only只对普通用户生效，对超级用户(root)不生效。建议使用web使用普通用户来访问数据库。

5 企业MySQL服务宕机的解决思路

5.1 MySQL服务器主库宕机

对于服务器宕机的问题，首页要做好计划，不要临时准备，不然会出现很多问题

进入从库，使用show slave status\G;

如果看见Slave_IO_State: Waiting for master to send event

表明从库是最新的，否则需要手动的获取主库的bin-log文件，手动对最新的一个从库进行数据恢复

然后进行一个已经是最新数据的主库，然后执行如下的命令：

stop slave; reset master;quit;

再进行从库的/data目录，删除master.info,relay-log.info

编辑从库的my.cnf,注释掉read-only和log-slave-updates等配置，打开bin-log

创建从库的用户

其他的从库需要stop slave;change master to new master;start slave;

最后修复宕机的主库，作为从库使用或者重新挂载到主库

在MySQL5.5支持一个半同步插件，保证一个从库和主库的数据是一致的，可以试试

5.2 从库的服务器宕机

在企业中，从库尽可能的多于两个，防止因一个从库宕机，导致服务不能进行正常的功能。

从库宕机很简单，删除所有的数据，从新做一个从库即可。

6 MySQL的存储引擎介绍

参考了别人的博客，下面是具体的连接

http://www.cnblogs.com/kevingrace/p/5685355.html

ppc_z

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫