mysql客户端/服务端搭建

mysql-devel:开发用到的库以及包含文件

mysql-client:客户端

mysql-server:数据库服务器

 

mysql官网 源码下载地址：

http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.16.tar.gz

http://dev.mysql.com/Downloads/mysql-5.7.16.tar.gz

mysql rpm下载地址：

http://ftp.ntu.edu.tw/MySQL/Downloads/MySQL-5.6/

percona下载地址:

https://www.percona.com/downloads

jdbc驱动地址：

https://dev.mysql.com/downloads/connector/j/5.1.html

 

 

mysql客户端-搭建

rpm -ivh MySQL-client-5.6.38-1.el7.x86_64.rpm

warning: MySQL-client-5.6.38-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

error: Failed dependencies:

/usr/bin/perl is needed by MySQL-client-5.6.38-1.el7.x86_64

##直接跳过依赖即可；如果不行，试试安装perl依赖     yum install -y perl-Module-Install.noarch

rpm -ivh MySQL-client-5.6.38-1.el7.x86_64.rpm --nodeps

##这样客户端即安装成功;尝试连接服务端：

mysql -h 1x.1xx.1xx.1xxx -u root -p

Enter password: root

 

 

mysql服务端-搭建

 

##环境准备

yum -y remove mysql-libs*                              ##卸载已有的mysql rpm包

yum install -y net-tools libaio* libnuma*           ##安装所需依赖包

 

##rpm包安装mysql           http://ftp.ntu.edu.tw/MySQL/Downloads/MySQL-5.6/

rpm -ivh MySQL-server-5.6.15-1.el6.x86_64.rpm

rpm -ivh MySQL-devel-5.6.15-1.el6.x86_64.rpm

rpm -ivh MySQL-client-5.6.15-1.el6.x86_64.rpm

 

##配置启动mysql

cp /usr/share/mysql/my-default.cnf /etc/my.cnf                     ##修改配置文件路径

/usr/bin/mysql_install_db                                                       ##初始化mysql及其密码

service mysql start                                                                 ##启动mysql

 

cat /root/.mysql_secret                                                          ##查看root账号密码

mysql -uroot -pqKTaFZnl                                                      ##登陆mysql

mysql> SET PASSWORD = PASSWORD('123456');          ##设置密码为123456

mysql> exit

mysql -uroot -p123456                                                  

 

##允许远程登陆

mysql> use mysql;

mysql> select host,user,password from user;

mysql> update user set password=password('123456') where user='root';

mysql> update user set host='%' where user='root' and host='localhost';

mysql> flush privileges;

mysql> exit

 

##设置开机启动

chkconfig mysql on

chkconfig --list | grep mysql

 

##mysql的默认安装位置说明

/var/lib/mysql/                   ##数据库目录

/usr/share/mysql               ##配置文件目录

/usr/bin                              ##相关命令目录

/etc/init.d/mysql                 ##启动脚本

 

##修改字符集和数据存储路径

##配置/etc/my.cnf文件,修改数据存放路径、mysql.sock路径以及默认编码utf-8.

vi /etc/my.cnf

[client]

password = 123456

port = 3306

default-character-set=utf8

[mysqld]

port = 3306

character_set_server=utf8

character_set_client=utf8

character-set-filesystem = utf8

collation-server=utf8_general_ci

##(注意linux下mysql安装完后是默认：表名区分大小写，列名不区分大小写； 0：区分大小写，1：不区分大小写)

lower_case_table_names=1

##(设置最大连接数，默认为 151，MySQL服务器允许的最大连接数16384; )

max_connections=1000

[mysql]

default-character-set = utf8

 

##查看字符集

mysql>show variables like '%collation%';

mysql>show variables like '%char%';

 

 

mysql服务端之mariadb方式搭建

yum -y install mariadb*

systemctl start mariadb.service

systemctl status mariadb.service

systemctl enable mariadb.service

 

mysql -u root -p                          ##回车进入

##给root用户全部权限（其他机器的hive客户端连接mysql时一定要赋权%）

mysql>grant all on *.* to root@'hostname本机' identified by 'root';

mysql>grant all on *.* to root@'localhost' identified by 'root';

mysql>grant all on *.* to root@'%' identified by 'root';

mysql>set password for root@localhost = password('root');                           ##修改密码

mysql>flush privileges;                                                                                    ##刷新授权表

 

egg:创建其它用户并赋权

create user 'hadoop' identified by 'hadoop';

grant all on *.* to 'hadoop'@'localhost' identified by 'hadoop';

grant all on *.* to 'hadoop'@'本机的hostname' identified by 'hadoop';

grant all on *.* to 'hadoop'@'%' identified by 'hadoop';

flush privileges;

 

设置utf-8字符编码

vi /etc/my.cnf

##在[mysqld]标签下添加

init_connect='SET collation_connection = utf8_unicode_ci'

init_connect='SET NAMES utf8'

character-set-server=utf8

 character-set-filesystem = utf8

collation-server=utf8_unicode_ci

skip-character-set-client-handshake

vi /etc/my.cnf.d/client.cnf

##在[client]中添加

default-character-set=utf8

vi /etc/my.cnf.d/mysql-clients.cnf

##在[mysql]中添加

default-character-set=utf8

 

##重启mariadb

systemctl restart mariadb

##进入MariaDB查看字符集

mysql>show variables like "%character%";show variables like "%collation%";