精华内容
下载资源
问答
  • LinuxMySQL是区分大小写的,在Windows下MySQL查询是不区分的, 当然,这个是可以设置的,如何设置网上有一大堆教程, 我就不班门弄斧啦,我发现区分大小写跟跟字符集也有关系,这就得说说utf8_bin跟utf8_...

    好久没来csdn上面写东西了,昨天面试官说想看看我的博客,才发现好久没用csdn了,因为我后面转用了有道云,无网的情况也能拿出来看。
    昨天被问到一个MySQL是否区分大小写,我第一反应是区分的,但是其中的原理却不大清楚,今天特意问了一下同事,也查询了一些资料,
    在Linux下MySQL是区分大小写的,在Windows下MySQL查询是不区分的,
    windows下

    当然,这个是可以设置的,如何设置网上有一大堆教程,
    我就不班门弄斧啦,我发现区分大小写跟跟字符集也有关系,这就得说说utf8_bin跟utf8_general_ci的区别 ,一下引用别人的研究—

    ci是 case insensitive, 即 “大小写不敏感”, a 和 A 会在字符判断中会被当做一样的;bin 是二进制, a 和 A
    会别区别对待.例如你运行:SELECT * FROM table WHERE txt = 'a’那么在utf8_bin中你就找不到 txt
    = ‘A’ 的那一行, 而 utf8_general_ci 则可以.

    展开全文
  • 下面的修改密码配置对于MySQL5.7不适用,5.7 的配置参照 http://blog.csdn.net/anshichuxuezhe/article/details/54140401 这篇文章,修改完密码之后需要看看user表中password_expired这个字段的值是不是大写的...

    下面的修改密码配置对于MySQL5.7不适用,5.7 的配置参照

    http://blog.csdn.net/anshichuxuezhe/article/details/54140401

    这篇文章,修改完密码之后需要看看user表中password_expired这个字段的值是不是大写的英文字母N,如果不是请改成N

    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    1.进入系统设置密码

    选择数据库:

    use mysql;

    修改密码:

    UPDATE user SET password=PASSWORD('123456') WHERE user='root';
    

    刷新:

    flush privileges;

    退出:

    exit;

    2.重启mysql服务

    service mysql restart

    3.忘记root密码

    把usr》share》mysql》my-medium.cnf 复制到etc下 重命名为my.cnf

    mysqld添加一行

    skip-grant-tables

    4.打开3306端口,适用于centos7以前的版本,之后版本参考

    contos firewalld 打开或关闭端口
     

    -A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT

    5.重启防火墙

    service iptables restart

    6.设置远程访问

    update user set host = '%' where user = 'root' limit 1; 

    7.设置数据库支持大小写

    lower_case_table_names = 1

    8.设置数据库编码级

    default-character-set=utf8
    
    character_set_server=utf8

    9.开启blog日志

    log-bin=/var/lib/mysql/mysql-bin

     

    展开全文
  • [root@localhost mysql-server-5.1.73]# cp /usr/share/doc/mysql-server-5.1.73/my-large.cnf /etc/my.cnf cp: overwrite `/etc/my.cnf'? y [root@localhost mysql-server-5.1.73]# vim /etc/my.cnf ...
    
    [root@localhost mysql-server-5.1.73]# cp /usr/share/doc/mysql-server-5.1.73/my-large.cnf  /etc/my.cnf
    cp: overwrite `/etc/my.cnf'? y
    [root@localhost mysql-server-5.1.73]# vim  /etc/my.cnf
    
    
    ----------
    
    
    # Example MySQL config file for large systems.
    #
    # This is for a large system with memory = 512M where the system runs mainly
    # MySQL.
    #
    # MySQL programs look for option files in a set of
    # locations which depend on the deployment platform.
    # You can copy this option file to one of those
    # locations. For information about these locations, see:
    # http://dev.mysql.com/doc/mysql/en/option-files.html
    #
    # In this file, you can use all long options that a program supports.
    # If you want to know which options a program supports, run the program
    # with the "--help" option.
    
    # The following options will be passed to all MySQL clients
    [client]
    #password       = your_password
    port            = 3306
    socket          = /var/lib/mysql/mysql.sock
    
    # Here follows entries for some specific programs
    
    # The MySQL server
    [mysqld]
    port            = 3306
    socket          = /var/lib/mysql/mysql.sock
    skip-locking
    key_buffer_size = 256M
    max_allowed_packet = 1M
    table_open_cache = 256
    sort_buffer_size = 1M
    read_buffer_size = 1M
    read_rnd_buffer_size = 4M
    myisam_sort_buffer_size = 64M
    thread_cache_size = 8
    query_cache_size= 16M
    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency = 8
    
    # Don't listen on a TCP/IP port at all. This can be a security enhancement,
    # if all processes that need to connect to mysqld run on the same host.
    # All interaction with mysqld must be made via Unix sockets or named pipes.
    # Note that using this option without enabling named pipes on Windows
    # (via the "enable-named-pipe" option) will render mysqld useless!
    #
    #skip-networking
    
    # Replication Master Server (default)
    # binary logging is required for replication
    log-bin=mysql-bin
    
    # binary logging format - mixed recommended
    binlog_format=mixed
    
    # required unique id between 1 and 2^32 - 1
    # defaults to 1 if master-host is not set
    # but will not function as a master if omitted
    server-id       = 1
    
    # Replication Slave (comment out master section to use this)
    #
    # To configure this host as a replication slave, you can choose between
    # two methods :
    #
    # 1) Use the CHANGE MASTER TO command (fully described in our manual) -
    #    the syntax is:
    #
    #    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
    #    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
    #
    #    where you replace <host>, <user>, <password> by quoted strings and
    #    <port> by the master's port number (3306 by default).
    #
    #    Example:
    #
    #    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
    #    MASTER_USER='joe', MASTER_PASSWORD='secret';
    #
    # OR
    #
    # 2) Set the variables below. However, in case you choose this method, then
    #    start replication for the first time (even unsuccessfully, for example
    #    if you mistyped the password in master-password and the slave fails to
    #    connect), the slave will create a master.info file, and any later
    #    change in this file to the variables' values below will be ignored and
    #    overridden by the content of the master.info file, unless you shutdown
    #    the slave server, delete master.info and restart the slaver server.
    #    For that reason, you may want to leave the lines below untouched
    #    (commented) and instead use CHANGE MASTER TO (see above)
    #
    # required unique id between 2 and 2^32 - 1
    # (and different from the master)
    # defaults to 2 if master-host is set
    # but will not function as a slave if omitted
    #server-id       = 2
    #
    # The replication master for this slave - required
    #master-host     =   <hostname>
    #
    # The username the slave will use for authentication when connecting
    # to the master - required
    #master-user     =   <username>
    #
    # The password the slave will authenticate with when connecting to
    # the master - required
    #master-password =   <password>
    #
    # The port the master is listening on.
    # optional - defaults to 3306
    #master-port     =  <port>
    #
    # binary logging - not required for slaves, but recommended
    #log-bin=mysql-bin
    
    # Uncomment the following if you are using InnoDB tables
    #innodb_data_home_dir = /var/lib/mysql
    #innodb_data_file_path = ibdata1:10M:autoextend
    #innodb_log_group_home_dir = /var/lib/mysql
    # You can set .._buffer_pool_size up to 50 - 80 %
    # of RAM but beware of setting memory usage too high
    #innodb_buffer_pool_size = 256M
    #innodb_additional_mem_pool_size = 20M
    # Example MySQL config file for large systems.
    #
    # This is for a large system with memory = 512M where the system runs mainly
    # MySQL.
    #
    # MySQL programs look for option files in a set of
    # locations which depend on the deployment platform.
    # You can copy this option file to one of those
    # locations. For information about these locations, see:
    # http://dev.mysql.com/doc/mysql/en/option-files.html
    #
    # In this file, you can use all long options that a program supports.
    # If you want to know which options a program supports, run the program
    # with the "--help" option.
    
    # The following options will be passed to all MySQL clients
    [client]
    #password       = your_password
    port            = 3306
    # This is for a large system with memory = 512M where the system runs mainly
    # MySQL.
    #
    # MySQL programs look for option files in a set of
    # locations which depend on the deployment platform.
    # You can copy this option file to one of those
    # locations. For information about these locations, see:
    # http://dev.mysql.com/doc/mysql/en/option-files.html
    #
    # In this file, you can use all long options that a program supports.
    # If you want to know which options a program supports, run the program
    # with the "--help" option.
    
    # The following options will be passed to all MySQL clients
    [client]
    default-character-set=utf8
    #password       = your_password
    port            = 3306
    socket          = /var/lib/mysql/mysql.sock
    
    # Here follows entries for some specific programs
    
    # The MySQL server
    [mysqld]
    lower_case_table_names=1
    port            = 3306
    socket          = /var/lib/mysql/mysql.sock
    skip-locking
    key_buffer_size = 256M
    max_allowed_packet = 1M
    table_open_cache = 256
    sort_buffer_size = 1M
    read_buffer_size = 1M
    read_rnd_buffer_size = 4M
    myisam_sort_buffer_size = 64M
    thread_cache_size = 8
    query_cache_size= 16M
    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency = 8
    
    # Don't listen on a TCP/IP port at all. This can be a security enhancement,
    # if all processes that need to connect to mysqld run on the same host.
    # All interaction with mysqld must be made via Unix sockets or named pipes.
    # Note that using this option without enabling named pipes on Windows
    # (via the "enable-named-pipe" option) will render mysqld useless!
    #
    #skip-networking
    
    # Replication Master Server (default)
    # binary logging is required for replication
    log-bin=mysql-bin
    
    # binary logging format - mixed recommended
    binlog_format=mixed
    
    # required unique id between 1 and 2^32 - 1
    # defaults to 1 if master-host is not set
    # but will not function as a master if omitted
    server-id       = 1
    
    # Replication Slave (comment out master section to use this)
    #
    # To configure this host as a replication slave, you can choose between
    # two methods :
    #
    # 1) Use the CHANGE MASTER TO command (fully described in our manual) -
    #    the syntax is:
    #
    #    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
    #    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
    #
    #    where you replace <host>, <user>, <password> by quoted strings and
    #    <port> by the master's port number (3306 by default).
    #
    #    Example:
    #
    #    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
    #    MASTER_USER='joe', MASTER_PASSWORD='secret';
    #
    # OR
    #
    # 2) Set the variables below. However, in case you choose this method, then
    #    start replication for the first time (even unsuccessfully, for example
    #    if you mistyped the password in master-password and the slave fails to
    #    connect), the slave will create a master.info file, and any later
    #    change in this file to the variables' values below will be ignored and
    #    overridden by the content of the master.info file, unless you shutdown
    #    the slave server, delete master.info and restart the slaver server.
    #    For that reason, you may want to leave the lines below untouched
    #    (commented) and instead use CHANGE MASTER TO (see above)
    #
    # required unique id between 2 and 2^32 - 1
    # (and different from the master)
    # defaults to 2 if master-host is set
    # but will not function as a slave if omitted
    #server-id       = 2
    #
    # The replication master for this slave - required
    #master-host     =   <hostname>
    #
    # The username the slave will use for authentication when connecting
    # to the master - required
    #master-user     =   <username>
    #
    # The password the slave will authenticate with when connecting to
    # the master - required
    #master-password =   <password>
    #
    # The port the master is listening on.
    # optional - defaults to 3306
    #master-port     =  <port>
    #
    # binary logging - not required for slaves, but recommended
    #log-bin=mysql-bin
    
    # Uncomment the following if you are using InnoDB tables
    #innodb_data_home_dir = /var/lib/mysql
    #innodb_data_file_path = ibdata1:10M:autoextend
    #innodb_log_group_home_dir = /var/lib/mysql
    # You can set .._buffer_pool_size up to 50 - 80 %
    # of RAM but beware of setting memory usage too high
    #innodb_buffer_pool_size = 256M
    #innodb_additional_mem_pool_size = 20M
    socket          = /var/lib/mysql/mysql.sock
    
    # Here follows entries for some specific programs
    
    # The MySQL server
    [mysqld]
    lower_case_table_names=1
    port            = 3306
    socket          = /var/lib/mysql/mysql.sock
    skip-locking
    key_buffer_size = 256M
    max_allowed_packet = 1M
    table_open_cache = 256
    sort_buffer_size = 1M
    read_buffer_size = 1M
    read_rnd_buffer_size = 4M
    myisam_sort_buffer_size = 64M
    thread_cache_size = 8
    query_cache_size= 16M
    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency = 8
    default-character-set=utf8
    # Don't listen on a TCP/IP port at all. This can be a security enhancement,
    "/etc/my.cnf" 148L, 4740C written                             
    [root@localhost mysql-server-5.1.73]# serv
    servertool  service   
    
    
    ----------
    [root@localhost mysql-server-5.1.73]# service mysqld restart
    ----------
    
    
    Stopping mysqld:  [  OK  ]
    Starting mysqld:  [  OK  ]

    转载于:https://www.cnblogs.com/ayhq/p/5839076.html

    展开全文
  • linux安装mysql全纪录[包括yum和rpm安装,编码,远程连接以及大小写问题] 一、查看mysql是否已经安装 使用“whereis mysql”命令来查看mysql安装路径: [root@hadoop01 ~]# whereis mysqlmysql: /usr/bin/mysql /...

    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;

     

    转载于:https://www.cnblogs.com/jun1019/p/6359067.html

    展开全文
  • java 调用linux命令需要制定路径+命令。 查看命令路径命令:whereis ***** ...mysql 中当字段存储格式为:utf8_general_ci时不区分大小写,也就是同时添加A,a将会报错,应将编码该为 utf8_general_cs ,utf8_bin
  • 好好耕耘 Mysql的表名/字段名/字段值是否区分大小写 1、MySQL默认情况下是否区分大小写...2、mysql对于类型为varchar数据默认不区分大小写,但如果该字段以“*_bin”编码的话会使mysql对其区分大小写。 3、mysql对...
  • 又有些日子没来iteye了,今天有空,来这写写东西,近日要准备做一个.net迁移java的项目,原先的项目全是.net,编码规范和风格,浓浓的微软风格。第一个切入口,把原有的新闻模块迁移过来,数据库还是原先的不变...
  • 想回顾一下:MySQLLinux下数据库名、表名、列名、别名大小写规则是这样的:1、数据库名与表名是严格区分大小写的;2、表的别名是严格区分大小写的;3、列名与列的别名在所有的情况下均是忽略大...
  • MySQL数据库在Wondows操作系统上默认不区分表名字母的大小写,在Linux系统上是区分大小写的,因此在实际编码过程中最好保持表名和字段名于数据库一致。 当然也可以通过相关配置,让MySQLLinux上不区分字母大小写。...
  • 前段时间,因为要用到mysql做个东西,但是却总是遇到乱码问题,网上给除了解决办法,但是,在我的电脑上老是行不通。虽然怀疑或是版本的问题,但是,却苦于不知道该怎么解决。今天无意中从linux公社中看到这篇文章,...
  • 于是安装在Linux下的Mysql导出到windows下可能因为大小写问题导致错误,因此要开启window下Mysql区分大小写。 进入Mysql安装目录,看看有没有my.ini; 没有的话,新建my.ini,添加如下内容(端口啥的自己改): ...
  • 想回顾一下:MySQLLinux下数据库名、表名、列名、别名大小写规则是这样的:1、数据库名与表名是严格区分大小写的;2、表的别名是严格区分大小写的;3、列名与列的别名在所有的情况下均是忽略大...
  • Linux Mysql配置

    2014-09-22 09:51:04
    1、Linuxmysql默认编码,修改/etc/mysql/my.cnf,在mysqld最后添加...2、修改mysql默认表名区分大小写问题,修改/etc/mysql/my.cnf,在mysqld最后添加。 [code="java"]lower_case_table_names=1[/code]...
  • 做一个积极的人 编码、改bug、提升自己 我有一个乐园,面向编程,...MySQLLinux下数据库名、表名、列名、别名大小写规则是这样的: 1、数据库名与表名是严格区分大小写的; 2、表的别名是严格区分大小写的...
  • mysql中是不区分大小写的,所以命令大小写都没关系; 下面这条命令指:创建一个名叫“test”的数据库(database),同时指定编码:utf8 CREATE DATABASE `test` CHARACTER SET utf8 COLLATE utf8_general_ci; ...
  • linux-mysql配置

    2020-09-14 16:10:34
    linux服务器mysql安装好后如果想修改配置,需找到my.cnf文件 /etc/my.cnf 一般目录是在 # For advice on how to change settings please see # ...#忽略表名大小写lower_case_table_n
  • UP只会分享与Java相关的学习资源本套视频共计时长约三十八个小时,该项目是一个大型电商项目,主要包括PC站、M站、APP客户端(Android、iOS)、数据接口系统、CMS管理系统、商家后台管理系统、财务系统、订单系统...
  • 其实这个程序真的很简单,十多分钟的事情,只是以前没在Linux下用纯C连过Mysql,想试一下,竟然搞了一整天,而且不是由于编码本身,是因为其他的一些周边问题,所以很有必要做一下笔记。 一、解决的问题: 1、...
  • mysql的一部分命令

    2020-12-14 21:40:15
    -mysql window 版本不区分大小写 linux区分 set password for root@localhost=rassword(‘密码’) create database 创建数据库名字; show databases;确认数据库有多少 查看所有 use 数据库名;打开数据库,使用...
  • MYSQL中文手册

    2013-03-11 21:21:34
    9.2.2. 识别符大小写敏感性 9.3. 用户变量 9.4. 系统变量 9.4.1. 结构式系统变量 9.5. 注释语法 9.6. MySQL中保留字的处理 10. 字符集支持 10.1. 常规字符集和校对 10.2. MySQL中的字符集和校对 10.3. 确定...
  • MySQL命令大全

    2018-01-15 11:19:17
    mysqldump -u 用名 -p –default-character-set=latin1 数据库名 > 导出的文件名(数据库默认编码是latin1) mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql 2.导出一个表 mysqldump -u 用户名 -p 数据库名 表名...
  • 1.设置MySQLLinux下)数据库的表名大小写敏感的问题 2.远端客户机登陆MySQL受阻:host 192.168.190.1 is not allowed to connect to this mysql server 3.MySQL不支持中文插入的解决(编码问题) 4.MySQL更新表...
  • 主要内容包括MySQL源代码的内部机制以及编码指导原则和如何维护源代码的最佳实践。  第4章介绍了生成高质量MySQL系统扩展的一个关键方面。这一章讲解了软件测试技术以及测试大型软件系统常用的实践方法,采用几个...
  • 客户用的数据库是mysql,而研发好的产品支持oracle,我们必须把数据库环境从oracle移植到mysql。...1、 大小写敏感的区别(如果服务器OS是linux)。 在oracle中一般情况下不区分大小写。有时候我们...
  • 不区分大小写; 支持中文编码; 连接设置相关(单IP连接次数、时间,客户端最大连接数); 定时自动备份数据; 本机备份和容灾备份,数据不会丢失,可迅速迁徙; 备份数据的过期删除; ... 环境: 云...

空空如也

空空如也

1 2 3 4
收藏数 72
精华内容 28
关键字:

linuxmysql大小写编码

linux 订阅
mysql 订阅