linux安装mysql全纪录[包括yum和rpm安装,编码,远程连接以及大小写问题]
linux安装mysql全纪录[包括yum和rpm安装,编码,远程连接以及大小写问题]
一、查看mysql是否已经安装
使用“whereis mysql”命令来查看mysql安装路径：
[root@hadoop01 ~]# whereis mysql
mysql: /usr/bin/mysql /usr/lib64/mysql /usr/share/mysql /usr/share/man/man1/mysql.1.gz
使用前提是你的MySQL不是解压版的，而是rpm包或者使用yum命令安装的；
备注：“whereis”命令用于查看软件的安装路径。
使用“rpm –qa|grep mysql”查看安装了哪些MySQL的rpm包：
[root@hadoop01 ~]# rpm -qa|grep mysql mysql-community-common-5.6.35-2.el6.x86_64 mysql-community-libs-5.6.35-2.el6.x86_64 mysql-community-release-el6-5.noarch mysql-community-client-5.6.35-2.el6.x86_64 mysql-community-server-5.6.35-2.el6.x86_64
二、卸载MySQL
[root@hadoop01 ~]# rpm -qa|grep mysql 先查看mysql的安装包有哪些 mysql-community-release-el6-5.noarch mysql-community-common-5.6.26-2.el6.i686 [root@hadoop01 ~]# yum remove mysql-community-release-el6-5.noarch mysql-community-common-5.6.26-2.el6.i686 删除安装包 Loaded plugins: fastestmirror, refresh-packagekit, security Setting up Remove Process Resolving Dependencies --> Running transaction check ---> Package mysql-community-common.i686 0:5.6.26-2.el6 will be erased ---> Package mysql-community-release.noarch 0:el6-5 will be erased --> Finished Dependency Resolution Dependencies Resolved ======================================================================================================================= Package Arch Version Repository Size ======================================================================================================================= Removing: mysql-community-common i686 5.6.26-2.el6 installed 2.1 M mysql-community-release noarch el6-5 @/mysql-community-release-el6-5.noarch 4.3 k Transaction Summary ======================================================================================================================= Remove 2 Package(s) Installed size: 2.1 M Is this ok [y/N]: y 输入y，确定 Downloading Packages: Running rpm_check_debug Running Transaction Test Transaction Test Succeeded Running Transaction Erasing : mysql-community-release-el6-5.noarch 1/2 Erasing : mysql-community-common-5.6.26-2.el6.i686 2/2 Verifying : mysql-community-common-5.6.26-2.el6.i686 1/2 Verifying : mysql-community-release-el6-5.noarch 2/2 Removed: mysql-community-common.i686 0:5.6.26-2.el6 mysql-community-release.noarch 0:el6-5 Complete! [root@hadoop01 ~]# rpm -qa|grep mysql 再次查看mysql的安装包 [root@hadoop01 ~]#
删除我们在mysql中创建的数据库：
MySQL创建的数据库一般位于“/var/lib/mysql”目录下，将“/var/lib/mysq”目录删除即可，否则在安装新的MySQL的时候，它发现已经有数据库了，就不会创建新的数据库了。
三、安装mysql
1.下载MySQL安装包
我们这里安装的mysql的版本是6.5。
MySQL的安装方式有两种，一种是用yum安装，yum会自动联网下载rpm包进行安装；一种是用rpm命令安装，需要自己手动下载MySQL的rmp安装包。我们这里先介绍使用yum安装方法。
首先到oracle官网下载“mysql-community-release-el6-5.noarch.rpm”，该文件很小，是MySQL的yum源包，里面只是记录了MySQL的yum地址。
2.安装mysql的yum仓库
将下载的源“mysql-community-release-el6-5.noarch.rpm”上传到linux服务器，使用yum命令安装此源：
yum localinstall mysql-community-release-el6-5.noarch.rpm
[root@hadoop01 mysql]# ll total 12 drwxr-xr-x. 2 root root 4096 Dec 7 04:34 mysql5.6-rpm -rw-r--r--. 1 root root 5824 Aug 1 2015 mysql-community-release-el6-5.noarch.rpm [root@hadoop01 mysql]# yum localinstall mysql-community-release-el6-5.noarch.rpm Loaded plugins: fastestmirror, refresh-packagekit, security Setting up Local Package Process Examining mysql-community-release-el6-5.noarch.rpm: mysql-community-release-el6-5.noarch Marking mysql-community-release-el6-5.noarch.rpm to be installed Loading mirror speeds from cached hostfile Resolving Dependencies --> Running transaction check ---> Package mysql-community-release.noarch 0:el6-5 will be installed --> Finished Dependency Resolution Dependencies Resolved =========================================================================================================== Package Arch Version Repository Size =========================================================================================================== Installing: mysql-community-release noarch el6-5 /mysql-community-release-el6-5.noarch 4.3 k Transaction Summary =========================================================================================================== Install 1 Package(s) Total size: 4.3 k Installed size: 4.3 k Is this ok [y/N]: y Downloading Packages: Running rpm_check_debug Running Transaction Test Transaction Test Succeeded Running Transaction Installing : mysql-community-release-el6-5.noarch 1/1 Verifying : mysql-community-release-el6-5.noarch 1/1 Installed: mysql-community-release.noarch 0:el6-5 Complete! [root@hadoop01 mysql]#
3.安装mysql
yum install mysql-server
执行此命令之前，必须保证linux虚拟机能够连接到外网，否则是安装不成的，只要能够连上外网，安装就不会有问题，下面是安装的记录：
[root@hadoop01 mysql]# yum install mysql-server Loaded plugins: fastestmirror, refresh-packagekit, security Setting up Install Process Loading mirror speeds from cached hostfile c6-media | 4.0 kB 00:00 mysql-connectors-community | 2.5 kB 00:00 mysql-connectors-community/primary_db | 13 kB 00:00 mysql-tools-community | 2.5 kB 00:00 mysql-tools-community/primary_db | 34 kB 00:00 mysql56-community | 2.5 kB 00:00 mysql56-community/primary_db | 176 kB 00:00 Package mysql-server is obsoleted by mysql-community-server, trying to install mysql-community-server-5.6.35-2.el6.x86_64 instead Resolving Dependencies --> Running transaction check ---> Package mysql-community-server.x86_64 0:5.6.35-2.el6 will be installed --> Processing Dependency: mysql-community-common(x86-64) = 5.6.35-2.el6 for package: mysql-community-server-5.6.35-2.el6.x86_64 --> Processing Dependency: mysql-community-client(x86-64) >= 5.6.10 for package: mysql-community-server-5.6.35-2.el6.x86_64 --> Processing Dependency: perl(DBI) for package: mysql-community-server-5.6.35-2.el6.x86_64 --> Running transaction check ---> Package mysql-community-client.x86_64 0:5.6.35-2.el6 will be installed --> Processing Dependency: mysql-community-libs(x86-64) >= 5.6.10 for package: mysql-community-client-5.6.35-2.el6.x86_64 ---> Package mysql-community-common.x86_64 0:5.6.35-2.el6 will be installed ---> Package perl-DBI.x86_64 0:1.609-4.el6 will be installed --> Running transaction check ---> Package mysql-community-libs.x86_64 0:5.6.35-2.el6 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================================= Package Arch Version Repository Size ================================================================================================= Installing: mysql-community-server x86_64 5.6.35-2.el6 mysql56-community 54 M Installing for dependencies: mysql-community-client x86_64 5.6.35-2.el6 mysql56-community 18 M mysql-community-common x86_64 5.6.35-2.el6 mysql56-community 308 k mysql-community-libs x86_64 5.6.35-2.el6 mysql56-community 1.9 M perl-DBI x86_64 1.609-4.el6 c6-media 705 k Transaction Summary ================================================================================================= Install 5 Package(s) Total download size: 75 M Installed size: 336 M Is this ok [y/N]: y Downloading Packages: (1/5): mysql-community-client-5.6.35-2.el6.x86_64.rpm | 18 MB 00:19 (2/5): mysql-community-common-5.6.35-2.el6.x86_64.rpm | 308 kB 00:00 (3/5): mysql-community-libs-5.6.35-2.el6.x86_64.rpm | 1.9 MB 00:02 (4/5): mysql-community-server-5.6.35-2.el6.x86_64.rpm | 54 MB 01:17 (5/5): perl-DBI-1.609-4.el6.x86_64.rpm | 705 kB 00:00 ------------------------------------------------------------------------------------------------- Total 767 kB/s | 75 MB 01:40 warning: rpmts_HdrFromFdno: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Retrieving key from file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql Importing GPG key 0x5072E1F5: Userid : MySQL Release Engineering <mysql-build@oss.oracle.com> Package: mysql-community-release-el6-5.noarch (@/mysql-community-release-el6-5.noarch) From : file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql Is this ok [y/N]: y Running rpm_check_debug Running Transaction Test Transaction Test Succeeded Running Transaction Installing : mysql-community-common-5.6.35-2.el6.x86_64 1/5 Installing : mysql-community-libs-5.6.35-2.el6.x86_64 2/5 Installing : mysql-community-client-5.6.35-2.el6.x86_64 3/5 Installing : perl-DBI-1.609-4.el6.x86_64 4/5 Installing : mysql-community-server-5.6.35-2.el6.x86_64 5/5 warning: /etc/my.cnf created as /etc/my.cnf.rpmnew Verifying : mysql-community-client-5.6.35-2.el6.x86_64 1/5 Verifying : mysql-community-common-5.6.35-2.el6.x86_64 2/5 Verifying : mysql-community-libs-5.6.35-2.el6.x86_64 3/5 Verifying : mysql-community-server-5.6.35-2.el6.x86_64 4/5 Verifying : perl-DBI-1.609-4.el6.x86_64 5/5 Installed: mysql-community-server.x86_64 0:5.6.35-2.el6 Dependency Installed: mysql-community-client.x86_64 0:5.6.35-2.el6
mysql-community-common.x86_64 0:5.6.35-2.el6
mysql-community-libs.x86_64 0:5.6.35-2.el6
perl-DBI.x86_64 0:1.609-4.el6 Complete! [root@hadoop01 mysql]# rpm -qa|grep mysql 查看已经安装的mysql的rpm包 mysql-community-common-5.6.35-2.el6.x86_64 mysql-community-libs-5.6.35-2.el6.x86_64 mysql-community-release-el6-5.noarch mysql-community-client-5.6.35-2.el6.x86_64 mysql-community-server-5.6.35-2.el6.x86_64 [root@hadoop01 mysql]# whereis mysql 查看mysql安装的路径 mysql: /usr/bin/mysql /usr/lib64/mysql /usr/share/mysql /usr/share/man/man1/mysql.1.gz [root@hadoop01 mysql]# [root@hadoop01 mysql]# ls /var/lib/mysql 我们创建的数据库都会存储在这个目录下面 [root@hadoop01 mysql]#
四、启动MySQL服务
启动mysql的命令：service mysqld status
安装完mysql之后，它就是一个名为“mysqld”的服务了，我们启动这个mysql服务即可。
下面是启动mysql服务的过程：
[root@hadoop01 mysql]# service mysqld status 查看mysql服务的状态 mysqld is stopped [root@hadoop01 mysql]# service mysqld start 启动mysql服务 Initializing MySQL database[开始初始化mysql数据库] : 2017-01-30 17:29:29 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated.
Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2017-01-30 17:29:29 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap. 2017-01-30 17:29:29 0 [Note] /usr/sbin/mysqld (mysqld 5.6.35) starting as process 3652 ... 2017-01-30 17:29:29 3652 [Note] InnoDB: Using atomics to ref count buffer pool pages 2017-01-30 17:29:29 3652 [Note] InnoDB: The InnoDB memory heap is disabled 2017-01-30 17:29:29 3652 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2017-01-30 17:29:29 3652 [Note] InnoDB: Memory barrier is not used 2017-01-30 17:29:29 3652 [Note] InnoDB: Compressed tables use zlib 1.2.3 2017-01-30 17:29:29 3652 [Note] InnoDB: Using Linux native AIO 2017-01-30 17:29:29 3652 [Note] InnoDB: Using CPU crc32 instructions 2017-01-30 17:29:29 3652 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2017-01-30 17:29:29 3652 [Note] InnoDB: Completed initialization of buffer pool 2017-01-30 17:29:29 3652 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created! 2017-01-30 17:29:29 3652 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB 2017-01-30 17:29:29 3652 [Note] InnoDB: Database physically writes the file full: wait... 2017-01-30 17:29:30 3652 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB 2017-01-30 17:29:30 3652 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB 2017-01-30 17:29:31 3652 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 2017-01-30 17:29:31 3652 [Warning] InnoDB: New log files created, LSN=45781 2017-01-30 17:29:31 3652 [Note] InnoDB: Doublewrite buffer not found: creating new 2017-01-30 17:29:31 3652 [Note] InnoDB: Doublewrite buffer created 2017-01-30 17:29:31 3652 [Note] InnoDB: 128 rollback segment(s) are active. 2017-01-30 17:29:31 3652 [Warning] InnoDB: Creating foreign key constraint system tables. 2017-01-30 17:29:31 3652 [Note] InnoDB: Foreign key constraint system tables created 2017-01-30 17:29:31 3652 [Note] InnoDB: Creating tablespace and datafile system tables. 2017-01-30 17:29:31 3652 [Note] InnoDB: Tablespace and datafile system tables created. 2017-01-30 17:29:31 3652 [Note] InnoDB: Waiting for purge to start 2017-01-30 17:29:31 3652 [Note] InnoDB: 5.6.35 started; log sequence number 0 2017-01-30 17:29:32 3652 [Note] Binlog end 2017-01-30 17:29:32 3652 [Note] InnoDB: FTS optimize thread exiting. 2017-01-30 17:29:32 3652 [Note] InnoDB: Starting shutdown... 2017-01-30 17:29:33 3652 [Note] InnoDB: Shutdown completed; log sequence number 1625977 2017-01-30 17:29:33 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated.
Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2017-01-30 17:29:33 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap. 2017-01-30 17:29:33 0 [Note] /usr/sbin/mysqld (mysqld 5.6.35) starting as process 3674 ... 2017-01-30 17:29:33 3674 [Note] InnoDB: Using atomics to ref count buffer pool pages 2017-01-30 17:29:33 3674 [Note] InnoDB: The InnoDB memory heap is disabled 2017-01-30 17:29:33 3674 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2017-01-30 17:29:33 3674 [Note] InnoDB: Memory barrier is not used 2017-01-30 17:29:33 3674 [Note] InnoDB: Compressed tables use zlib 1.2.3 2017-01-30 17:29:33 3674 [Note] InnoDB: Using Linux native AIO 2017-01-30 17:29:33 3674 [Note] InnoDB: Using CPU crc32 instructions 2017-01-30 17:29:33 3674 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2017-01-30 17:29:33 3674 [Note] InnoDB: Completed initialization of buffer pool 2017-01-30 17:29:33 3674 [Note] InnoDB: Highest supported file format is Barracuda. 2017-01-30 17:29:33 3674 [Note] InnoDB: 128 rollback segment(s) are active. 2017-01-30 17:29:33 3674 [Note] InnoDB: Waiting for purge to start 2017-01-30 17:29:33 3674 [Note] InnoDB: 5.6.35 started; log sequence number 1625977 2017-01-30 17:29:33 3674 [Note] Binlog end 2017-01-30 17:29:33 3674 [Note] InnoDB: FTS optimize thread exiting. 2017-01-30 17:29:33 3674 [Note] InnoDB: Starting shutdown... 2017-01-30 17:29:35 3674 [Note] InnoDB: Shutdown completed; log sequence number 1625987 [你要记住为root用户设置一个密码！] PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h hadoop01 password 'new-password' Alternatively you can run: /usr/bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. Please report any problems at http://bugs.mysql.com/ The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at http://shop.mysql.com Note: new default config file not created. Please make sure your config file is current WARNING: Default config file /etc/my.cnf exists on the system This file will be read by default by the MySQL server If you do not want to use this, either remove it, or use the --defaults-file argument to mysqld_safe when starting the server [ OK ] Logging to '/var/lib/mysql/hadoop01.err'. Starting mysqld: [ OK ] [root@hadoop01 mysql]# service mysqld status 再次查看mysql服务的状态 mysqld (pid 3839) is running... [root@hadoop01 mysql]#
五、 设置root用户的密码
为root用户设置密码，然后使用root用户连接mysql服务器：
[root@hadoop01 mysql]# /usr/bin/mysqladmin -u root password 'root' 为root用户设置密码 Warning: Using a password on the command line interface can be insecure. [root@hadoop01 mysql]# mysql -uroot –proot 使用root用户连接mysql服务器 Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; 查看数据库列表 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) mysql>
六、 远程连接MySQL服务器
远程连接mysql数据库
我们想使用Navicat for MySQL连接mysql数据库服务器，出现如下错误提示：
错误提示：不允许远程连接mysql数据库服务器！需要授权才能访问。
Mysql的远程连接授权：
[root@hadoop01 mysql]# mysql -uroot -proot Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.6.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION; Query OK, 0 rows affected (0.01 sec) mysql>
再次远程连接mysql服务器：
RPM包安装方式：
如果你的电脑不能联网的话，就不能使用yum的方式来安装mysql的服务了， 可以使用参考资料中的mysql-rpm文件夹下的mysql安装包来安装。
[root@bogon mysql-rpm]# rpm -ivh mysql-community-*
安装后，启动服务、设置密码、远程授权后既可以使用。
七、字符编码问题
我们的mysql数据库创建好了，先看下面的一个问题：
[root@hadoop01 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.6.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.03 sec) mysql> create database xuebusi; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | xuebusi | +--------------------+ 4 rows in set (0.00 sec) mysql> use xuebusi; Database changed mysql> create table tb1(id bigint, name varchar(250)); Query OK, 0 rows affected (0.09 sec) mysql> show tables; +-------------------+ | Tables_in_xuebusi | +-------------------+ | tb1 | +-------------------+ 1 row in set (0.00 sec) mysql> insert into tb1 values(1, 'zhangsan'); Query OK, 1 row affected (0.05 sec) mysql> select * from tb1; +------+----------+ | id | name | +------+----------+ | 1 | zhangsan | +------+----------+ 1 row in set (0.00 sec) mysql> insert into tb1 values(2, '李四'); ERROR 1366 (HY000): Incorrect string value: '\xE6\x9D\x8E\xE5\x9B\x9B' for column 'name' at row 1 mysql>
在上面的记录中，我们使用命令行简单创建了一个xubusi数据库，并创建了一张名为tb1的表，插入英文的数据是没有问题的，但是插入中文时报错了：
ERROR 1366 (HY000): Incorrect string value: '\xE6\x9D\x8E\xE5\x9B\x9B' for column 'name' at row 1
意思是说，name这个字段插入了一个不合法的值。
为什么插入中文就不合法了呢？
因为我们建表的时候，没有为name这个字段指定字符集，所以它默认采用的字符集是“latin1”。
打开Navicat for MySQL工具，在tb1表名上面单击鼠标右键选择“设计表”，选中“name”这一列，在下方就会显示该列对应的字符集：
在建表的时候我们当然可以为每一个字段指定字符集，如果没有指定的话，varchar类型的字段的默认字符集就是“latin1”。前面需要把它手动修改成“utf8”，才能插入中文。
不仅字段有默认的字符集，表也有默认的字符集，如果建表时没有为表指定字符集，表的默认字符集也是“latin1”，我们也要把表的字符集也手动修改为utf8：
查看mysql的默认字符集并修改：
[root@hadoop01 ~]# vi /etc/my.cnf 修改mysql的默认字符集 character_set_database=utf8 # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html [client] 新增[client]标签 default-character-set=utf8 新增此配置 [mysqld] default-storage-engine=INNODB 新增此配置 character-set-server=utf8 新增此配置 collation-server=utf8_general_ci 新增此配置 # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... # socket = ..... # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M #lower_case_table_names=1 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES ~ ~ ~ "/etc/my.cnf" 34L, 1094C written [root@hadoop01 ~]# service mysqld restart 重启mysql服务 Stopping mysqld: [ OK ] Starting mysqld: [ OK ] [root@hadoop01 ~]# cat /etc/my.cnf 查看修改后的字符集 # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html [client] default-character-set=utf8 [mysqld] default-storage-engine=INNODB character-set-server=utf8 collation-server=utf8_general_ci # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... # socket = ..... # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M #lower_case_table_names=1 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [root@hadoop01 ~]#
mysql> show variables like '%char%'; 未修改编码之前，查看字符集设置如下： ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 2 Current database: xuebusi +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.01 sec) mysql> exit; 修改/etc/my.cnf文件后，退出命令行，重新登录 Bye [root@hadoop01 ~]# mysql -uroot –p 重新登录 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show variables like '%char%'; 再次查看字符集设置，都改成utf8了 +--------------------------+----------------------------+ | 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 | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec) 修改了mysql的默认字符集之后，我们使用命令行来创建数据库和表来看一下默认的建库和建表的字符集： mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | xuebusi | +--------------------+ 4 rows in set (0.00 sec) mysql> create database db_1; 创建一个db_1数据库 Query OK, 1 row affected (0.01 sec) mysql> show create database db_1; 查看建库的sql语句 +----------+---------------------------------------------------------------+ | Database | Create Database | +----------+---------------------------------------------------------------+ | db_1 | CREATE DATABASE `db_1` /*!40100 DEFAULT CHARACTER SET utf8 */ | 此时数据库默认的建库字符集编码是utf8编码 +----------+---------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> use db_1; Database changed mysql> create table tb_1(id int(10),name varchar(50)); 在db_1数据库中创建tb_1表 Query OK, 0 rows affected (0.06 sec) mysql> show create table tb_1; 查看tb_1表的建表SQL语句 +-------+----------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------+ | tb_1 | CREATE TABLE `tb_1` ( `id` int(10) DEFAULT NULL, `name` varchar(50) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 此时默认的建表字符集是utf8编码 +-------+----------------------------------------------------+ 1 row in set (0.00 sec) mysql>
但是，如果使用Navicat for MySQL工具建库或者建表的话，需要手动指定表和字段的编码。
通过Navicat for MySQL建库的时候，须手动指定数据库的字符编码：
要查看已经创建好的数据库的字符编码，可以在表名上点击鼠标右键，选择“数据库属性”，在弹出的窗口中可以查看。
通过Navicat for MySQL建表时，不用为每一字段都指定字符编码，在保存表之前，切换的“选项”这个选项卡中，可以手动设置表的字符编码为utf8编码，然后再保存：
查看已经创建好的表个建表sql语句，可以在表名上点击数据右键，选择“对象信息”，再点击“DDL”选项卡，就可以看到建表sql语句了：
在Navicat for MySQL中往“db_2”数据库的“tb_2”中插入几条数据，包含中文：
通过命令行来查看“db_2”数据库的“tb_2”中数据，中文显示也是正常的：
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db_1 | | db_2 | | mysql | | performance_schema | | xuebusi | +--------------------+ 6 rows in set (0.00 sec) mysql> use db_2; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------+ | Tables_in_db_2 | +----------------+ | tb_2 | +----------------+ 1 row in set (0.00 sec) mysql> select * from tb_2; +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 2 | 李四 | | 3 | 王五 | +----+----------+ 3 rows in set (0.00 sec) mysql>
八、关于大小写问题
在windows下，mysql不区分大小写，而在linux下，mysql是严格区分大小写的。
看下面的建表sql语句，建表时指定的表名是大写的“TB_3”，往表中插入数据以及查询表数据的时候也要保证表名是大写的“TB_3”，否则会报错：
mysql> CREATE TABLE `TB_3` ( -> `ID` bigint(20) NOT NULL, -> `NAME` varchar(255) DEFAULT NULL, -> PRIMARY KEY (`ID`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.04 sec) mysql> insert into tb_3 values(1, '李四'); ERROR 1146 (42S02): Table 'db_2.tb_3' doesn't exist mysql> insert into TB_3 values(1, '李四'); Query OK, 1 row affected (0.01 sec) mysql> select * from tb_3; ERROR 1146 (42S02): Table 'db_2.tb_3' doesn't exist mysql> select * from TB_3; +----+--------+ | ID | NAME | +----+--------+ | 1 | 李四 | +----+--------+ 1 row in set (0.00 sec) mysql> show variables like '%case%'; 查看默认的大小写设置，在linux下，lower_case_table_names=0表示严格区分大小写 +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | lower_case_file_system | OFF | | lower_case_table_names | 0 | +------------------------+-------+ 2 rows in set (0.00 sec) mysql>
原来Linux下的MySQL默认是区分表名大小写的，通过如下设置，可以让MySQL不区分表名大小写：
1、用root登录，修改 /etc/my.cnf；
2、在[mysqld]节点下，加入一行： lower_case_table_names=1；
3、重启MySQL即可；
其中 lower_case_table_names=1 参数缺省地在 Windows 中这个选项为 1 ，在 Unix 中为 0，因此在window中不会遇到的问题，一旦一直到linux就会出问题的原因（尤其在mysql对表起名时是无法用大写字母的，而查询用了大写字母却会出查不到的错误，真是弄的莫名其妙）：
[root@hadoop01 ~]# vi /etc/my.cnf # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html [client] default-character-set=utf8 [mysqld] default-storage-engine=INNODB character-set-server=utf8 collation-server=utf8_general_ci lower_case_table_names=1 增加此项配置 # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... # socket = ..... # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M #lower_case_table_names=1 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES ~ ~ ~ "/etc/my.cnf" 34L, 1118C written [root@hadoop01 ~]# service mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ] [root@hadoop01 ~]#
mysql> show variables like '%case%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | lower_case_file_system | OFF | | lower_case_table_names | 1 | +------------------------+-------+ 2 rows in set (0.00 sec) mysql> exit; Bye [root@hadoop01 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db_1 | | db_2 | | mysql | | performance_schema | | xuebusi | +--------------------+ 6 rows in set (0.00 sec) mysql> use db_2; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------+ | Tables_in_db_2 | +----------------+ | TB_3 | | tb_2 | +----------------+ 2 rows in set (0.00 sec) mysql> select * from TB_3; ERROR 1146 (42S02): Table 'db_2.tb_3' doesn't exist mysql> drop table TB_3; ERROR 1051 (42S02): Unknown table 'db_2.tb_3' mysql> show tables; +----------------+ | Tables_in_db_2 | +----------------+ | TB_3 | | tb_2 | | tb_user | +----------------+ 3 rows in set (0.00 sec) mysql> select * from tb_user; +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 2 | 李四 | | 3 | 王wu | +----+----------+ 3 rows in set (0.00 sec) mysql> select * from TB_USER; +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 2 | 李四 | | 3 | 王wu | +----+----------+ 3 rows in set (0.00 sec) mysql>
在“/etc/my.cnf”文件中增加了“lower_case_table_names=1”，重启mysqld之后，发现原来的“db_2”数据库下面的“TB_3”表无法操作了，提示“Table 'db_2.tb_3' doesn't exist”！
解决办法，修改“/etc/my.cnf”文件，删除“lower_case_table_names=1”，重启mysqld服务，然后将“TB_3”由大写修改成小写的“tb_3”，然后再在“/etc/my.cnf”文件中添加“lower_case_table_names=1”设置，并重启mysqld服务。这样“tb_3”表又可以操作了：
mysql> select * from tb_3; 查询时使用小写的表名 +----+--------+ | ID | NAME | +----+--------+ | 1 | 李四 | +----+--------+ 1 row in set (0.00 sec) mysql> select * from TB_3; 查询时使用大写的表名 +----+--------+ | ID | NAME | +----+--------+ | 1 | 李四 | +----+--------+ 1 row in set (0.00 sec) mysql>
九、网上的一篇介绍MySQL大小写问题的文章
MySQL大小写敏感说明 经常遇到的问题，一些不是特别重要但是又比较郁闷的事情。例如今天这个MySQL大小写敏感。 先上测试结果。 Linux环境下，不是windows平台下。区别很大。注意。 一图胜千言 mysql> show create table Ac; +-------+--------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------+ | Ac | CREATE TABLE `Ac` ( `a` varchar(20) DEFAULT NULL, `c` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+--------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> insert into Ac values ('1q','1q'); Query OK, 1 row affected (0.00 sec) mysql> insert into Ac values ('1Q','1Q'); Query OK, 1 row affected (0.00 sec) mysql> select * from Ac WHERE a='1q'; +------+------+ | a | c | +------+------+ | 1q | 1q | | 1Q | 1Q | +------+------+ 2 rows in set (0.00 sec) mysql> select * from AC ; ERROR 1146 (42S02): Table 'test.AC' doesn't exist mysql> select * from Ac where A='1Q'; +------+------+ | a | c | +------+------+ | 1q | 1q | | 1Q | 1Q | +------+------+ 2 rows in set (0.00 sec) 如上的结果能反应说明以下结论。 MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的： 1、数据库名与表名是严格区分大小写的； 2、表的别名是严格区分大小写的； 3、列名与列的别名在所有的情况下均是忽略大小写的； 4、字段内容默认情况下是大小写不敏感的。 mysql中控制数据库名和表名的大小写敏感由参数lower_case_table_names控制，为0时表示区分大小写，为1时，表示将名字转化为小写后存储，不区分大小写。 mysql> show variables like '%case%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | lower_case_file_system | OFF | | lower_case_table_names | 0 | +------------------------+-------+ 2 rows in set (0.00 sec) 修改cnf配置文件或者编译的时候，需要重启服务。 MySQL存储的字段是不区分大小写的。这个有点不可思议。尤其是在用户注册的业务时候，会出现笑话。所以还是严格限制大小写敏感比如好。 如何避免字段内容区分大小写。就是要新增字段的校验规则。 可以看出默认情况下字段内容是不区分大小写的。大小写不敏感。 mysql> create table aa (a varchar(20) BINARY , c varchar(20)) ; Query OK, 0 rows affected (0.10 sec) mysql> show create table aa; +-------+-------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------+ | aa | CREATE TABLE `aa` ( `a` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `c` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+-------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from aa; +------+------+ | a | c | +------+------+ | a | C | | a | C | | A | c | +------+------+ 3 rows in set (0.00 sec) mysql> select * from aa where a = 'a'; +------+------+ | a | c | +------+------+ | a | C | | a | C | +------+------+ 2 rows in set (0.00 sec) mysql> select * from aa where a = 'A'; +------+------+ | a | c | +------+------+ | A | c | +------+------+ 1 row in set (0.00 sec) 原因如下: 字段值的大小写由mysql的校对规则来控制。提到校对规则，就不得不说字符集。字符集是一套符号和编码，校对规则是在字符集内用于比较字符的一套规则 . 一般而言，校对规则以其相关的字符集名开始，通常包括一个语言名，并且以_ci（大小写不敏感）、_cs（大小写敏感）或_bin（二元）结束 。
比如 utf8字符集，utf8_general_ci,表示不区分大小写，这个是utf8字符集默认的校对规则；utf8_general_cs表示区分大小写，utf8_bin表示二进制比较，
同样也区分大小写 。 记录下！
十、在网上一篇解决MySQL字符编码的文章
彻底解决mysql中文乱码的办法 2013/9/24 18:06:44来源：www.pc6.com作者：gm MySQL会出现中文乱码的原因不外乎下列几点： 1.server本身设定问题，例如还停留在latin1 2.table的语系设定问题(包含character与collation) 3.客户端程式(例如php)的连线语系设定问题 强烈建议使用utf8!!!! utf8可以兼容世界上所有字符!!!! Linux下Mysql插入中文显示乱码解决方案 mysql -uroot -p 回车输入密码 进入mysql查看状态如下： mysql不能插入中文 默认的是客户端和服务器都用了latin1，所以会乱码。 解决方案: mysql>user mydb; mysql>alter database mydb character set utf8;! 另一种方法是直接修改默认的Mysql配置文件 在debian环境下，彻底解决mysql无法插入中文和中文乱码的办法 上面提到了用临时方法更改数据库的字符集设置，显示中文，但是后来发现在有的系统下并不能成功，
比如我用的debian 7.0，找了好久终于找到一个适合debian系统的解决方案，debian 7.0下测试成功，
其他诸如修改client和mysql加入default-character-set=utf8的方法之类的，只适用与5.5以前的系统！ 终极决绝方案，一劳永逸： debian下mysql的字符串修改为utf8(参考:rainysia的专栏) 一、进入mysql(mysql -uroot -p),查看当前数据库字符集(status;) 二、查看当前系统版本(lsb_release -a) Mysql不能插入中文 三、#vim /etc/mysql/my.cnf 。(5.5以前系统)在【client】下面加入 default-character-set=utf8 在【mysqld】下面加入default-character-set=utf8 Notice：注意 如果修改后不能启动报错试试把default-character-set=utf8改为character_set_server=utf8，
仅仅加入到mysqld下面的.client就不需要加了 四、#vim /etc/mysql/my.cnf 。(5.5以后系统)如下修改： [client] default-character-set=utf8 [mysqld] default-storage-engine=INNODB character-set-server=utf8 collation-server=utf8_general_ci 五、重启mysql(/etc/init.d/mysql stop /etc/init.d/mysql start) 六、修成成功，进入mysql查看字符集(mysql>show variables like ‘character_set_%’;) Mysql无法插入中文 一、避免创建数据库及表出现中文乱码和查看编码方法 1、创建数据库的时候：CREATE DATABASE `test` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'; 2、建表的时候 CREATE TABLE `database_user` ( `ID` varchar(40) NOT NULL default '', `UserID` varchar(40) NOT NULL default '', ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 这3个设置好了，基本就不会出问题了,即建库和建表时都使用相同的编码格式。 但是如果你已经建了库和表可以通过以下方式进行查询。 1.查看默认的编码格式: mysql> show variables like "%char%"; +--------------------------+---------------+ | Variable_name | Value | +--------------------------+---------------+ | character_set_client | gbk | | character_set_connection | gbk | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | gbk | | character_set_server | utf8 | | character_set_system | utf8 | +--------------------------+-------------+ 注：以前2个来确定,可以使用set names utf8,set names gbk设置默认的编码格式; 执行SET NAMES utf8的效果等同于同时设定如下： SET character_set_client='utf8'; SET character_set_connection='utf8'; SET character_set_results='utf8'; 2.查看test数据库的编码格式: mysql> show create database test; +------------+------------------------------------------------------------------------------------------------+ | Database | Create Database | +------------+------------------------------------------------------------------------------------------------+ | test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET gbk */ | +------------+------------------------------------------------------------------------------------------------+ 3.查看yjdb数据库的编码格式: mysql> show create table yjdb; | yjdb | CREATE TABLE `yjdb` ( `sn` int(5) NOT NULL AUTO_INCREMENT, `type` varchar(10) NOT NULL, `brc` varchar(6) NOT NULL, `teller` int(6) NOT NULL, `telname` varchar(10) NOT NULL, `date` int(10) NOT NULL, `count` int(6) NOT NULL, `back` int(10) NOT NULL, PRIMARY KEY (`sn`), UNIQUE KEY `sn` (`sn`), UNIQUE KEY `sn_2` (`sn`) ) ENGINE=MyISAM AUTO_INCREMENT=1826 DEFAULT CHARSET=gbk ROW_FORMAT=DYNAMIC | 二、避免导入数据有中文乱码的问题 1:将数据编码格式保存为utf-8 设置默认编码为utf8： set names utf8; 设置数据库db_name默认为utf8: ALTER DATABASE `db_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 设置表tb_name默认编码为utf8: ALTER TABLE `tb_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 导入： LOAD DATA LOCAL INFILE 'C:\\utf8.txt' INTO TABLE yjdb; 2:将数据编码格式保存为ansi(即GBK或GB2312) 设置默认编码为gbk： set names gbk; 设置数据库db_name默认编码为gbk: ALTER DATABASE `db_name` DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; 设置表tb_name默认编码为gbk: ALTER TABLE `tb_name` DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; 导入： LOAD DATA LOCAL INFILE 'C:\\gbk.txt' INTO TABLE yjdb; 注：1.UTF8不要导入gbk，gbk不要导入UTF8; 2.dos下不支持UTF8的显示; 三、解决网页中乱码的问题 将网站编码设为 utf-8,这样可以兼容世界上所有字符。 如果网站已经运作了好久,已有很多旧数据,不能再更改简体中文的设定,那么建议将页面的编码设为 GBK,
GBK与GB2312的区别就在于:GBK能比GB2312显示更多的字符,要显示简体码的繁体字,就只能用GBK。 1.编辑/etc/my.cnf ,在[mysql]段加入default_character_set=utf8; 2.在编写Connection URL时，加上?useUnicode=true&characterEncoding=utf-8参; 3.在网页代码中加上一个"set names utf8"或者"set names gbk"的指令，告诉MySQL连线内容都要使用 utf8或者gbk;
