mysql 集群字符集_mysql字符集以及乱码实战

单实例启动

/etc/init.d/mysqld start

1启动mysql_safe --datadir =? --pid=? other args >/dev/null 2>&1 &

2 初始化数据库时mysql输出给出的启动方法

mysql_safe --user=mysql&

提示：

当找回root密码时，会经常使用mysql_safe --user=mysql &带参数启动

2当我们自己开发启动脚本启动数据库时可能会用到这个启动方法

3 /etc/init.d/mysqld 和mysql_safe -user=mysql启动实质是一样的

三常规方法关闭数据库

etc/init.d/mysqld stop

强制关闭数据库的方法

killall mysqld直到no process 就完成了

pkill mysqld

killall -9 mysqld

找回丢失的mysql root密码

启动修改丢失的mysql单实例root的方法

1首先停止数据库

/etc/init.d/mysqld stop 多实例killall mysqld

2使用--skip-grant-tables 启动数据库，胡月授权登录验证

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

多实例 mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-table &

或者mysql -uroot -p -S /data/3306/mysql.socket

mysql

update password

不能set password=passwd('')

flush privileges

quit

重启

mysqladmin shutdown

/etc/init.d/mysqld start

mysqladmin -uroot -p‘密码’ shutdown

/etc/init.d/mysqld start

#######################################################################没做#########################################################

mysql登录方式

mysql

mysql -uroot

mysql -uroot -p

mysql -uroot -p'admin'

HISTORY=ignorespace 不记录铭感记录如

history -c

history -d 2清除前两项记录

cat /root/.mysqlhistory

cat /root/.bash里有history记录linux命令

mysql提示符修改\u系统

mysql> prompt \u@root  \r:\m:\s->

PROMPT set to '\u@root  \r:\m:\s->'

或者my.cnf [mysql]里添加prompt \u@root  \r:\m:\s->

多实例而远程连接 mysql -uroot -padmin -h 127.0.0.1 -P3306

学会mysql帮助命令

linux man help内置命令 help history

oot@root  10:36:54->help

nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

root@root  10:38:13->help show

Name: 'SHOW'

root@root 10:38:19->

查看权限

help show

help show grants

help show grants for 'root'@'localhost'

退出mysql exit quit ctr+c ctr+d

安全设置

a 为用户设置密码

b删除无用的mysql库内账号

c删除默认存在的test数据库

更安全的做法是删除root用户delete from mysql.user

2>新增system并且提升为超级管理员用户

mysql> grant all privileges on *.* to system@'localhost' identified by 'admin' with grant option

-> ;revoke

Query OK, 0 rows affected (0.02 sec)

为管理员root设置密码

mysqladmin -uroot password'admin'

mysqladmin -uroot -p'admin'  password'admin' -S /data/3308/mysql.socket

修改

mysqladmin -uroot -padmin password'admin'

mysqladmin -uroot -p'admin'  password'admin' -S /data/3308/mysql.socket

mysql>update mysql.user set password=password('admin') where user='root'

mysql>flush privileges;

第三种

mysql>set password =pasword('admin')

找回丢失的密码

mysql> create database tt;

Query OK, 1 row affected (0.05 sec)

mysql> show  create database tt;

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

| Database | Create Database                                               |

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

| tt       | CREATE DATABASE `tt` /*!40100 DEFAULT CHARACTER SET latin1 */ |

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

1 row in set (0.05 sec)

mysql> create database tt_gbk DEFAULT CHARACTER SET gbk collate gbk_chinese _ci;

mysql> create database tt_gbk DEFAULT CHARACTER SET utf8 collate utf8_general_ci;

Query OK, 1 row affected (0.00 sec)

如果编译的时候制定了数据库的字符集就不需要制定字符集了

根据开发环境制定字符集 或编译的时候制定

编译的时候没有制定字符集或者开发制定了不同的字符集， 那么指定字符集创建即可

显示数据库

mysql> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| test               |

| tt                 |

| tt_gbk             |

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

6 rows in set (0.04 sec)

mysql> show databases like '%tt%'

-> ;

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

| Database (%tt%) |

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

| tt              |

| tt_gbk          |

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

2 rows in set (0.05 sec)

当前数据库

mysql> use tt

Database changed

mysql> select database();查看当前连接的数据库

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

| database() |

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

| tt         |

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

1 row in set (0.00 sec)

删除数据库

mysql> drop database tt_gbk;

Query OK, 0 rows affected (0.00 sec)

连接数据库

mysql> use tt

mysql> select now();

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

| now()               |

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

| 2015-12-28 00:16:59 |

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

1 row in set (0.00 sec)

mysql> select user();

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

| user()         |

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

| root@localhost |

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

1 row in set (0.00 sec)

mysql> select version();

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

| version()  |

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

| 5.5.32-log |

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

1 row in set (0.00 sec)

创建mysql用户及其授予权限

create user 'jeffrey'@'localhost' identified by 'mypass';

grant all on db1.* to 'jeffrey'@'localhost'

grant select on db2.invoice to 'jeffrey' @'localhost'

grant usage on *.* to 'jeffrey'@'localhost' with max_queries_per_hour 90

可以通过查看grant 命令帮助

mysql> grant all privileges on test.* to test02@'localhost' identified by 'admin';

Query OK, 0 rows affected (0.04 sec)

mysql> select user,host from mysql.user;

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

| user   | host                  |

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

| root   | 127.0.0.1             |

| root   | ::1                   |

|        | localhost             |

| root   | localhost             |

| system | localhost             |

| test02 | localhost             |

|        | localhost.localdomain |

| root   | localhost.localdomain |

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

8 rows in set (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

mysql> show grants for test02@localhost;

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

| Grants for test02@localhost                                                                                   |

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

| GRANT USAGE ON *.* TO 'test02'@'localhost' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441' |

| GRANT ALL PRIVILEGES ON `test`.* TO 'test02'@'localhost'                                                      |

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

2 rows in set (0.00 sec)

useage 什么都不能做