远程连接以及大小写标题,将项目配置到阿里云服务器ECS计算

一.概述

  上一章讲到了RPM安装后的文件目录,那章仍旧介绍下安装步骤。也便从此做参考吧。

  1. 移出centos 7系统自带的mysql库

  yum remove mysql-libs 

    997755.com澳门葡京 1

  2. 将下载的mysql包放到/home/hsr/tool
目录下(mysql-5.7.20-1.el7.x86_64.rpm-bundle.tar)

   997755.com澳门葡京 2
  3. 解压到mysql文件夹下的rpm包
    997755.com澳门葡京 3

  4.必备的4个rpm安装

  rpm -ivh mysql-community-common-5.7.20-1.el7.x86_64.rpm

997755.com澳门葡京,    997755.com澳门葡京 4

rpm -ivh mysql-community-libs-5.7.20-1.el7.x86_64.rpm

    997755.com澳门葡京 5

rpm -ivh mysql-community-client-5.7.20-1.el7.x86_64.rpm

    997755.com澳门葡京 6

rpm -ivh mysql-community-server-5.7.20-1.el7.x86_64.rpm

    997755.com澳门葡京 7
  5. 查看服务情形
     997755.com澳门葡京 8
  6.翻望初步密码

cat /var/log/mysqld.log | grep password

    997755.com澳门葡京 9
  7.复制出密码登录
    997755.com澳门葡京 10
  8. 改动密码
    登录成功后在创设库里会提醒如下:
    997755.com澳门葡京 11

SET PASSWORD = PASSWORD('js*2015');

    997755.com澳门葡京 12

    上图Reset密码时:提醒您当前的密码不相符政策要求,也就是太简单。把密码级别设到最低

set global validate_password_policy=0;

    997755.com澳门葡京 13
    再重置密码:

SET PASSWORD = PASSWORD('js*2015%');

    997755.com澳门葡京 14
  9.允许远程连接

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'js*2015%' WITH GRANT OPTION;  

    997755.com澳门葡京 15
  那样,大家就足以在windows电脑上用mysql客户端远程连接linux上的mysql服务了。如果在windows电脑上三番五次不了,先用telnet
来拼下ip和端口,检查防火墙

 

linux安装mysql全纪录[包罗yum和rpm安装,编码,远程连接以及大小写标题]

 

1.由此速龙lij Idea 将品种打包成war 包

生成war包过程:

997755.com澳门葡京 16

卷入步骤

扭转后的war 包地点: /项目名/out/项目名_war/项目名_war.war

997755.com澳门葡京 17

变化后的war 包

一、查看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”命令用于查看软件的设置路径。

远程连接以及大小写标题,将项目配置到阿里云服务器ECS计算。动用“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

 

  随着云服务器的普及,购入云服务器的妙方越来越低,对一个程序员来说,很三人会购买一款云服务器。以前买过两年windows服务器(没有啥样实际用途,就是为着玩),近期有机会接触一下linux服务器,接纳了系统为centos7的阿里云服务器,下边和豪门一起来学学一下linux服务器的各样操作(纯新手)。

2. 因此SSH Secure File Transfer 连接到远程服务器

取得云服务器ECS
的公网ip地址,以及登录名和登录密码(即远程CentOS服务器上的用户名和登录密码)

997755.com澳门葡京 18

一连到长途服务器

连接到控制台

997755.com澳门葡京 19

4.gif

二、卸载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的时候,它发现早已有数据库了,就不会创建新的数据库了。

  个人对linux服务器的精晓,java开发的程序三大件事apache+tomcat+mysql(那个明白可能有误,我是.net开发),入手的率先件是安装mysql数据库。

3. CentOS 上安装 jdk1.8

三、安装mysql

  首先用xshell连接受centos7,第五遍进入多少不适于,毕竟用windows系统习惯了。

下载

为了操作便利,在windows上下载好 jdk 的 压缩包,通过 SSH file transfer
拖进 远程服务器中

jdk1.8下载地址

注意:选择 jdk-8u144-linux-x64.tar.gz 等以 tar.gz 结尾压缩包

将压缩包复制到远程CentOS系统中

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地址。

997755.com澳门葡京 20

 

  997755.com澳门葡京 21  

设置配备jdk

1)查看当前Linux系统是还是不是曾经设置java

rpm -qa | grep java

2)卸载已部分 java

rpm -e --nodeps 安装包名

3)安装jdk运行须求的插件

yum install glibc.i686

4 ) 解压jdk到/usr/local下

 tar –xvf  jdk-8u144-linux-x64.tar.gz –C /usr/local

5)配置jdk环境变量,打开/etc/profile布署文件,将上边配置拷贝进去

#set java environment
JAVA_HOME=/usr/local/jdk1.8.0_144
CLASSPATH=.:$JAVA_HOME/lib/tools.jar
PATH=$JAVA_HOME/bin:$PATH
export JAVA_HOME CLASSPATH PATH

6 ) 重新加载/etc/profile安排文件

source /etc/profile

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]#

  997755.com澳门葡京 22

4.安装MySql 5.7

鉴于MySQL被Oracle收购,所以从Centos7开首不再默许安装mysql而用玛丽亚db代替,需求利用一些办法才能科学安装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]#

 

 

  名称写一个和好喜欢的,主机填写云服务器公网ip,用户名填root,密码填写建服务器时设置的密码(忘记的话去阿里云上修修改改)。

1.先是查看是还是不是有玛丽亚db,有则卸载玛丽亚db,否则安装Mysql会发生争执
rpm –qa | grep mariadb #查看mariadb安装包  
rpm -e --nodeps mariadb-libs-xxxxxxxxx.  #卸载mariadb 

自我批评系统自带的MySQL及相关RPM包,是或不是安装

rpm -qa | grep -i mysql

即使有安装,则移除(rpm –e 名称)

yum -y remove 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]#

 

 

  进入一个黑框中(没有桌面是否有点蒙)。

2.下载安装 mysq57 的 rpm 包

从官网下载 rpm
包mysql57-community-release-el7-11.noarch.rpm,并从windows
复制到 CentOS 服务器上

997755.com澳门葡京 23

image.png

复制到CentOS服务器后,安装 rpm 包

rpm -ivh mysql57-community-release-el7-11.noarch.rpm

五、 设置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数据库,

3.设置mysql环境及其依赖包
yum install mysql mysql-server mysql mysql-devel  

六、 远程连接MySQL服务器

远程连接mysql数据库

俺们想行使Navicat for MySQL连接mysql数据库服务器,出现如下错误提醒:

997755.com澳门葡京 24

颠倒是非提醒:不允许远程连接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服务器:

997755.com澳门葡京 25

RPM包安装格局:

一经你的微处理器不可以联网的话,就无法使用yum的法门来设置mysql的劳务了,
可以利用参考资料中的mysql-rpm文件夹下的mysql安装包来安装。

[root@bogon mysql-rpm]# rpm -ivh mysql-community-*

 

设置后,启动服务、设置密码、远程授权后既可以接纳。

  1.在指令框中数据 rpm -qa|grep
mysql,什么都尚未出现,表示从未设置mysql

4.启动mysql 服务
systemctl start mysqld.service #启动mysql服务  
systemctl enable mysqld.service #设置开机启动mysql服务  

七、字符编码难题

俺们的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”这一列,在凡间就会显得该列对应的字符集:

997755.com澳门葡京 26

在建表的时候大家当然可以为每一个字段指定字符集,假若没有点名的话,varchar类型的字段的默许字符集就是“latin1”。前边须要把它手动修改成“utf8”,才能插入汉语。

 

非但字段有默许的字符集,表也有默许的字符集,假使建表时没有为表指定字符集,表的默认字符集也是“latin1”,我们也要把表的字符集也手动修改为utf8:

 997755.com澳门葡京 27

997755.com澳门葡京 28

翻开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>

 

997755.com澳门葡京 29

 

 

997755.com澳门葡京 30

 

不过,假诺应用Navicat for
MySQL工具建库或者建表的话,必要手动指定表和字段的编码。

 

通过Navicat for MySQL建库的时候,须手动指定数据库的字符编码:

997755.com澳门葡京 31

 

要翻看已经创制好的数据库的字符编码,可以在表名上点击鼠标右键,接纳“数据库属性”,在弹出的窗口中得以查看。

 

通过Navicat for
MySQL建表时,不用为每一字段都指定字符编码,在保存表此前,切换的“选项”那些选项卡中,可以手动设置表的字符编码为utf8编码,然后再保存:

997755.com澳门葡京 32

翻开已经创立好的表个建表sql语句,可以在表名上点击数据右键,选拔“对象新闻”,再点击“DDL”选项卡,就足以观望建表sql语句了:

997755.com澳门葡京 33

在Navicat for MySQL中往“db_2”数据库的“tb_2”中插入几条数据,包括汉语:

997755.com澳门葡京 34

因而命令行来查阅“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>

   997755.com澳门葡京 35

5.登录mysql

鉴于 mysql 5.7 版本在装置时会创制随机密码,故得先找到该密码
位置: /var/log/mysqld.log

vim /var/log/mysqld.log

997755.com澳门葡京 36

随意生成的密码

mysql -u root -p
Enter password:     #输入生成的随机密码

八、关于大小写标题

在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>

  由于centos7应用的是玛丽亚db,所以执行yum install
mysql命令只是更新玛丽亚db数据库(具体是如何不懂,我是安装好多次后搜出来的)

6.改动密码
mysql>ALTER USER USER() IDENTIFIED BY '12345678'

实施后报错:

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

原因:mysql 5.7 对密码的扑朔迷离有了须要
化解措施:

mysql> set global validate_password_policy=0;
mysql> set global validate_password_length=4;  
mysql> ALTER USER USER() IDENTIFIED BY '12345678';

九、网上的一篇介绍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表示二进制比较,
同样也区分大小写 。

记录下!

 

  2.下载mysql的repo源wget

7. 再度登陆
# mysql> exit 
# mysql -u root -p

十、在网上一篇解决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;

 

  997755.com澳门葡京 37

8. 开启mysql的远程登录

默许情状下mysql为平安起见,不帮忙远程登录mysql,所以须求设置开启
远程登录mysql的权杖
报到mysql后输入如下命令:

grant all privileges on *.* to 'root' @'%' identified by 'root';
flush privileges;

  安装mysql57-community-release-el7-11.noarch.rpm包

9. 安装mysql 远程访问

1.率先将 3306 端口添加到防火墙开放端口

firewall-cmd --permanent --zone=public --add-port=3306/tcp

报错?防火墙怎么打开?点击那

2.重启防火墙

systemctl restart firewalld.service

3.云服务器ECS添加安全组

在造访ECS服务器的某个端口时,会被阿里云屏蔽掉,因此须要将该端口添加到安全组(即设置为可以被访问)

997755.com澳门葡京 38

丰富安全组

997755.com澳门葡京 39

立异后的安全组

4.通过sqlyog 远程连接CentOS 服务器上的mysql

997755.com澳门葡京 40

image.png

  执行命令rpm -ivh mysql57-community-release-el7-11.noarch.rpm

6.安装 Tomcat

  在/etc/yum.repos.d/文件下会有七个mysql资源包

1.下载tomcat

在windows上下载tomcat9,
然后复制到 CentOS服务器中

997755.com澳门葡京 41

image.png

  997755.com澳门葡京 42

2.解压启动

解压

tar -xvf apache-tomcat-9.0.0.M26.tar.gz -C /usr/local

取得的文本夹名太长,更改为tomcat

cd /usr/local
mv apache-tomcat-9.0.0.M26/  tomcat

进入 /usr/local/tomcat/bin下,运行 startup.sh

cd /usr/local/tomcat/bin
./startup.sh

  3.执行安装mysql命令yum install mysql-server,中间会问Is this
ok?输入y按回车就可以了

7.安装tomcat远程访问

1.第一将 8080端口添加到防火墙开放端口

firewall-cmd --permanent --zone=public --add-port=8080/tcp

报错?防火墙怎么打开?点击这

2.重启防火墙

systemctl restart firewalld.service

3.云服务器ECS添加安全组

997755.com澳门葡京 43

现今外网就足以透过 云服务器ECS 公网ip+端口号访问tomcat下的项目了

http://43.198.32.32:8080

   997755.com澳门葡京 44

壮大: 将tomcat 设置为80端口访问

  再度翻开是或不是安装数据库,突显如下结果注脚安装成功。

1.进入/usr/local/tomcat/conf/下,修改里面的server.xml文件

997755.com澳门葡京 45

cd /usr/local/tomcat/conf
vim server.xml

  997755.com澳门葡京 46

2.重启tomcat服务器
cd /usr/local/tomcat/bin
./shutdown.sh
./startup.sh

  4.是否到这就感觉万事大吉了,还有坑要求你去解决,首先是登陆难题,

3.率先将 8080端口添加到防火墙开放端口
firewall-cmd --permanent --zone=public --add-port=8080/tcp

报错?防火墙怎么打开?点击那

   mysql5.7系统会自动给生成一个即兴密码,在mysqld.log中,输入指令可以查阅的到

4.重启防火墙
systemctl restart firewalld.service

  997755.com澳门葡京 47

5.将80端口添加到阿里云ECS安全组

997755.com澳门葡京 48

此时,就可以直接通过ip地址访问tomcat服务器了(不输入端口号,默许访问的是80端口)

   登录后修改密码,指示以下错误

  997755.com澳门葡京 49

  重置密码,提示不适合密码安全策略

  997755.com澳门葡京 50

  那里必要修改以下安全策略,validate_password_policy默认是1,validate_password_length默许是8(最小是4,写0默许依旧4),那是和谐测试用的,实际中要么指出强密码

  997755.com澳门葡京 51

  其次假使想远程连接到mysql数据,要求以下两步操作:

  (1)阿里云服务器安全组设置,登录到阿里云后台,点击越来越多,选用安全组配置,点击配置规则,添加安全组规则,重要的手续如下图

  997755.com澳门葡京 52

   997755.com澳门葡京 53

  然后当地测试一下服务器的3306端口(windows默许没有打开telnet客户端,必要协调打开)

  997755.com澳门葡京 54

  回车出现xxxx is not allowed to connect to this MySQL
server,那些根本是mysql不容许远程登录,须要设置一下

  1.mysql -u root -p 输入密码登录mysql

  2.update user set host = ‘%’ where user = ‘root’;

  3.flush privileges;(刷新MySQL的权杖相关表)

  然后再行测试telnet,可以见见出现新的窗口,3306方可健康访问。

   (2)防火墙设置,先查看防火墙状态(这一步看个人必要,提议拉开)

  997755.com澳门葡京 55

  可以观望阿里云服务器默许是关闭防火墙的,并且开机不自行启动,需求丰硕3306端口,并设置成开机自动启动。

   997755.com澳门葡京 56

  用Navicat连接一下

  997755.com澳门葡京 57

   好了,有标题欢迎大家谈谈。

  参考地址:

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website