mysql

mysql5.7

关系型数据库SQL mysql mariadb oracle DB2

非关系型数据库(NOSQL):只在内存里存储，没有具体的位置。全球有100多种 redis memcached MongoDB

A. DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程、函数，CREATE DROP ALTER //开发人员

B. DML语句数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE //开发人员

C. DQL语句数据库查询语言： 查询数据SELECT

D. DCL语句 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE

源码安装MySQL：

源码安装mysql时候，需要先安装boost库

1. 编译安装

# yum -y install ncurses ncurses-devel openssl-devel bison gcc gcc-c++ make

cmake:

# yum -y install cmake

boost:

# wget http://sourceforge.net/projects/boost/files/boost/1.59.0/boost_1_59_0.tar.gz

mysql:

# groupadd mysql

# useradd -r -g mysql -s /bin/false mysql

# tar xvf mysql-5.7.19.tar.gz

# cd mysql-5.7.19

[root@mysql3 mysql-5.7.19]# pwd

/root/mysql-5.7.19

[root@mysql3 mysql-5.7.19]# tar xf /root/boost_1_59_0.tar.gz

3.清空系统残留并创建新的账户

userdel -r mysql

yum -y remove mariadb mariadb-lib mariadb-server mariadb-devel

rm -rf /etc/my*

rm -rf /var/lib/mysql

useradd -r mysql -M -s /sbin/nologin

[root@mysql-5.7.17 ~]# cmake . \ 指定当前目录

-DWITH_BOOST=boost_1_59_0/ \

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \  指定安装目录

-DSYSCONFDIR=/etc \            配置文件的位置，默认就是etc

-DMYSQL_DATADIR=/usr/local/mysql/data \    数据目录 错误日志文件

-DINSTALL_MANDIR=/usr/share/man \    帮助文档的目录

-DMYSQL_TCP_PORT=3306 \                默认端口号3306

-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \    用来做网络通信，启动的时候才会产生

-DDEFAULT_CHARSET=utf8 \          默认字符集

-DEXTRA_CHARSETS=all \

-DDEFAULT_COLLATION=utf8_general_ci \

-DWITH_READLINE=1 \                可以上下翻历史命令

-DWITH_SSL=system \

-DWITH_EMBEDDED_SERVER=1 \  嵌入式服务器

-DENABLED_LOCAL_INFILE=1 \  支持从本机导入

-DWITH_INNOBASE_STORAGE_ENGINE=1  默认存储引擎

提示：boost也可以使用如下指令自动下载

-DDOWNLOAD_BOOST=1

# make

# make install

2. 初始化

[root@mysql1 local]# cd mysql              把这个删了就相当于卸载

[root@mysql1 mysql]# mkdir mysql-files

[root@mysql1 mysql]# chown -R mysql.mysql .

[root@mysql1 mysql]# ./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data  初始化，只需要初始化一次

[root@mysql1 mysql]# ./bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data

建立MySQL配置文件my.cnf

[root@mysql1 mysql]# vim /etc/my.cnf

[mysqld]

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

启动MySQL

方法二：使用centos6 mysql.server脚本(system V)

[root@mysql1 mysql]# cp support-files/mysql.server /etc/init.d/mysqld

[root@mysql1 mysql]# chkconfig --add mysqld

[root@mysql1 mysql]# chkconfig mysqld on

[root@mysql1 mysql]# service mysqld start

查看库 show databases; show create database +库名；

进入库 use +库名

创建表 create table class；

查看表 show tables； show create table class;

查看表状态 show table status like '+表名' \G

查看表结构 desc class；

查看表的创建过程 show create table t1

使用\G结尾，按记录显示，每条记录显示一次

删除表 drop table class

删除表字段 alter table cless drop ename；

修改字段

modify ： 不可以改名 alter table info modify name char(20) after age;

change ：可以改名 alter table info change name ename char(20) ;

添加记录： insert into info(ename,age) values("tom",24);

更新：

更新记录：update info set grade=4 where ename="yingge";

查询 select * from class

删除记录：delete from info where id=170301;

delete from info ; 将表内所用记录删除

删除表 drop table class;

删除库 drop database +库名

查看数据库

mysql> show databases;

mysql> show create database wing;

mysql> select database();查看当前所在的库

切换数据库

mysql> use wing;

mysql> show tables;

删除数据库

DROP DATABASE 数据库名;

mysql -u root -p1 -e "use db2;create table t3(name char(20),pass char(100));insert into t3 set name='wing',pass=password('123')"

mysql> desc emp; 查看表结构

mysql> show create table emp;  查看详细的表内容

mysql> show create table emp \G

竖着看旋转90度，一条一条记录的显示

mysql> show table status like 'emp' \G

创建表 create table

查看表结构 desc table, show create table

修改表 alter table

删除表 drop table

插入数据 insert

INSERT INTO 表名 VALUES (值1,值2,值3…值n);

更新数据 update

UPDATE 表名 SET 字段1=值1,字段2=值2, WHERE CONDITION;

删除数据 delete

DELETE FROM 表名 WHERE CONITION;

刷新权限 mysql > flush privileges;

grant all on wing.t1 to 'xiaowu'@'172.16.70.%' identified by '123';

撤销权限 mysql> revoke all on . from 'xiaowu'@'%';

删除账户： mysql> drop user wing;

创建账户： mysql> create user wing;

mysql -h192.168.5.240 -P 3306 -u root -p123 mysql -e ‘select user,host from user’

-h指定主机名 【默认为localhost】

-PMySQL服务器端口 【默认3306】

-u指定用户名 【默认root】

-p指定登录密码 【默认为空密码】

此处mysql为指定登录的数据库 -e接SQL语句

修改用户密码:

mysqladmin -uroot -p'123' password 'new_password'   //123为旧密码

root修改其他用户密码

set password for user3@’localhost’=password(‘new_password’);



UPDATE mysql.user SET authentication_string=password(‘new_password’)

WHERE user=’user3’ AND host=’localhost’;

FLUSH PRIVILEGES;



SET password=password(‘new_password’);

grant权限列表on库名.表名to '用户名'@'客户端主机' [identified by '密码' with option参数];

==权限列表all 所有权限（不包括授权权限）

==客户端主机%所有主机

with_option参数

GRANT OPTION： 授权选项

MAX_QUERIES_PER_HOUR： 定义每小时允许执行的查询数

MAX_UPDATES_PER_HOUR： 定义每小时允许执行的更新数

MAX_CONNECTIONS_PER_HOUR： 定义每小时可以建立的连接数

MAX_USER_CONNECTIONS： 定义单个用户同时可以建立的连接数

/etc/my.cnf

error log 错误日志 排错/var/log/mysqld.log【默认开启】

bin log 二进制日志 备份 增量备份 DDL DML DCL

Relay log 中继日志复制 接收 replication master

slow log 慢查询日志调优 查询时间超过指定值

mysqldump 备份： 逻辑备份

备份表：mysqldump -u root -p1 db1 t1 t2 > /db1.t1_t2.bak

恢复： # mysqldump -u root -p1 db1 t1  t2 < /db1.t1.bak

备份多个库     #mysqldump  -u root -p1 -B  db1 db2 db3 > /db123.bak

备份所有的库         #mysqldump  -u root -p1 -A > /alldb.bak

恢复数据库:

停止数据库对外的服务.   mysql> set sql_log_bin=0           关闭2进制

mysql> source  db1.t1.bak

或者

#mysql -u root -p1 -D  db1 < db1.t1.bak

或

cat  db1.t1.bak | mysql -u root -p1 -D  db1





-A, --all-databases           备份所有库

-B,--databases bbs test mysql         备份多个数据库

percona-xtrabackup开源免费的支持MySQL 数据库热备份的软

完全备份流程:innobackupex --user=root --password='Qianfeng123!@' /xtrabackup/full

完全备份恢复流程

1. 停止数据库

2. 清理环境

3. 重演回滚－－> 恢复数据

4. 修改权限

5. 启动数据库

关闭数据库：

# systemctl stop mysqld

# rm -rf /var/lib/mysql/*

# rm -rf /var/log/mysqld.log

# rm -rf /var/log/mysql-slow/slow.log

恢复之前的验证恢复：

# innobackupex --apply-log /xtrabackup/full/2018-01-21_18-19-25/

确认数据库目录：

恢复之前需要确认配置文件内有数据库目录指定，不然xtrabackup不知道恢复到哪里

# cat /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

恢复数据：

[root@wing mysql]# innobackupex --copy-back /xtrabackup/full/2018-01-21_18-19-25/

修改权限：

[root@wing mysql]# chown mysql.mysql  /var/lib/mysql  -R

增量备份流程

完整： innobackupex --user=root --password=123 /xtrabackup/

增量备份

innobackupex --user=root --password=123 --incremental /xtrabackup/zeng/ --incremental-basedir=/xtrabackup/2018-01-24_15-29-08/

先恢复完整在增量

完整： innobackupex --user=root --password=123 /xtrabackup/

差异备份

# innobackupex --user=root --password=888--incremental /xtrabackup--incremental-basedir=/xtrabackup/完全备份目录（周一）277D5D393EE}

恢复先完整 在差异

差异是基于完整备份 增量是基于上一次可以是增量或完整

数据导入导出

导入数据

mysql> load data infile '/tmp/db5.t3.bak' into table t4;

mysql> load data infile '/tmp/db5.t3.bak' into table t4 fields terminated by ','  lines terminated by '\n';  以什么作为分割

导出数据

mysql> select * from t3 into outfile '/tmp/db5.t3.bak';

mysql> select * from t3 into outfile '/tmp/db5.t3.bak1' fields terminated by ',' lines terminated by '\n';

AB复制 主从 M--S 主从没有数据的情况

1.主从都关闭防火墙selinux

#/etc/init.d/iptables stop

#setenforce 0

2.主

安装软件mysql mysql-server

配置：

#vim  /etc/my.cnf

[mysqld]

log-bin = mylog                     开启2进制

server-id = 1            

创建账户：

mysql> grant replication  slave,reload,super  on *.*  to  'slave'@'%'  identified by  '123';

mysql> flush privileges;

启动服务：

#/etc/init.d/mysqld  start

注意:删除以前的binlog日志

3.从

安装软件

配置：

#vim  /etc/my.cnf

[mysqld]

server-id = 2

master-host =  172.16.70.250

master-user = slave

master-password = 123

启动服务：

#/etc/init.d/mysqld  start

测试：

登录slave

mysql> start slave;
mysql> show slave status \GP;

gtid_mode=ON 用这个模块做主主 2进制日志自动同步

M

vim /etc/my.cnf

log_bin

server-id=1

gtid_mode=ON

enforce_gtid_consistency=1



mysqldump -p'QianFeng@123' --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F`-mysql-all.sql

S

mysql> change master to

master_host='master1',

master_user='rep',

master_password='QianFeng@123',

master_auto_position=1;



start slave;

show slave status\G;

MS

主(master1)：

开启二进制日志

master1 M

[root@localhost ~]# vim /etc/my.cnf

log_bin

server-id=1

[root@localhost ~]# systemctl restart mysqld              重启生效

grant replication slave, replication client on *.* to 'rep'@'192.168.2.%'  identified by 'QianFeng@123';

          向你的S【从】授权

做备份：

mysqldump -p'QianFeng@123' --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F`-mysql-all.sql

观察二进制日志分割点

CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000002', MASTER_LOG_POS=154;

S      mysql -h master1 -urep -p'QianFeng@123'          远程登录  验证         之前授权

[root@localhost ~]#vim /etc/my.cnf

server-id=2
log_bin

恢复手动同步数据

mysql>  set sql_log_bin=0;

mysql>  source /tmp/2017-1-1-mysql-full.sql

设置主服务器

mysql> change master to

master_host='master1',                                                M 主的IP

master_user='rep',                                                         登录用户名

master_password='QianFeng@123',                            登录密码

master_log_file='localhost-bin.000002',

master_log_pos=154;

注意，二进制日志的位置，应该参照主服务器备份时生成的新位置。

启动从设备 mysql> start slave; show slave status\G; 查状态

M 主

1 启动二进制日志，服务器ID，GTID

vim /etc/my.cnf

log_bin

server-id=1

gtid_mode=ON

enforce_gtid_consistency=1

systemctl restart mysqld

grant replication slave,replication client on *.* to 'rep'@'192.168.122.%' identified by 'QianFeng@123';

flush privileges;

mysqldump -p'QianFeng@123' --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F`-mysql-all.sql

S 从

mysql -h master1 -urep -p'QianFeng@123'

vim /etc/my.cnf

log_bin

server-id=2

gtid_mode=ON

enforce_gtid_consistency=1

systemctl restart mysqld

3 还恢复手动同步数据

mysql> set sql_log_bin=0;

mysql> source /tmp/2017-1-1-mysql-full.sql

mysql> select * from master1db.master1tab;

4 设置主服务器

mysql> change master to

master_host='master1',

master_user='rep',

master_password='QianFeng@123',

master_auto_position=1;

start slave;

show slave status\G;

MMSS部署

MM

两台master1 master2 相互授权

mysql> grant replication slave, replication client on *.* to 'rep'@'192.168.2.%' identified by 'QianFeng@123';

mysql> flush privileges;

彼此设置对方为  master

mysql> change master to

master_host='master2',                      master的   IP

master_user='rep',

master_password='QianFeng@123',

master_auto_position=1;

start slave;           show slave status\G;

如果要用gtid模块就同时在 vim /etc/my.cnf 2进制日志自动同步

log_bin                     2进制日志本来就要开启

server-id=1                 作为标识

gtid_mode=ON                       用gtid模块时加入

enforce_gtid_consistency=1         用gtid时加入

SS

mysqldump -p'QianFeng@123' --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F`-mysql-all.sql

# mysql -p'QianFeng@123' < /tmp/2017-1-1-mysql-all.sql



vim /etc/my.cnf

server-id=3

gtid_mode=ON

enforce_gtid_consistency=1

master-info-repository=TABLE

relay-log-info-repository=TABLE

mysql> change master to

master_host='master1',

master_user='rep',

master_password='QianFeng@123',

master_auto_position=1 for channel 'master1';



mysql> change master to

master_host='master2',

master_user='rep',

master_password='QianFeng@123',

master_auto_position=1 for channel 'master2';

SS 有2个master MM就是MS 与SM

---

client mysql master write

\ / |

mysql-proxy-------- / \

/ \ / \

client mysql mysql slave read

---

MySQL读写分离：

mysql-cluster 官方集群

引擎必须是NDB

manager 不给客户端提供任何服务

1. 规划

拓扑图 ip 主机名称

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

| manager节点 sql节点 data节点

|

| 172.16.70.20 172.16.70.89

|172.16.70.76

| 172.16.70.83 172.16.70.70

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

2. 修改ip，主机名称，解析

1 #!/bin/bash

2 echo '172.16.70.76 manager.up.com'  >> /etc/hosts

3 echo '172.16.70.20 sql1_20.up.com'  >> /etc/hosts

4 echo '172.16.70.83 sql2_83.up.com'  >> /etc/hosts

5 echo '172.16.70.89 data1_89.up.com' >> /etc/hosts

6 echo '172.16.70.70 data2_70.up.com' >> /etc/hosts

5. 安装

安装节点，四台机器，需要重复四次：（管理节点不用安装mysql-cluster-gpl-6.3.20-linux-i686-glibc23.tar.gz，可以从其他4台机器拷贝出ndbd_mgmd，ndbd_mgm两个程序。）

#useradd mysql

#tar zxvf mysql-cluster-gpl-6.3.20-linux-i686-glibc23.tar.gz

#mv mysql-cluster-gpl-6.3.20-linux-i686-glibc23 /usr/local/mysql

#chown mysql:mysql /usr/local/mysql

上述命令先创建mysql组和mysql用户，并且把mysql分配到mysql组，然后将安装文件解压，把它放置到/usr/local/mysql目录。.

6. 配置

管理节点配置：通过配置文件让管理节点知道所有的集群节点的基本信息

# mkdir /usr/local/mysql/cluster-conf

# vim /usr/local/mysql/cluster-conf/config.ini

[ndbd default]

NoOfReplicas=2

DataMemory=80M

IndexMemory=18M

[ndb_mgmd]

id=1

hostname=172.16.70.76

datadir=/var/lib/mysql-cluster

[ndbd]

id=2

hostname=172.16.70.89

datadir=/usr/local/mysql/ndbdata

[ndbd]

id=3

hostname=172.16.70.70

datadir=/usr/local/mysql/ndbdata

[ndbd]

id=4

hostname=172.16.70.20

[ndbd]

id=5

hostname=172.16.70.83

# chown mysql.mysql /usr/local/mysql/cluster-conf -R

# mkdir /var/lib/mysql-cluster

# chown mysql.mysql /var/lib/mysql-cluster

2个数据节点配置：

[root@data1_89 ~]# mkdir /usr/local/mysql/ndbdata

[root@data1_89 ~]# chown mysql.mysql /usr/local/mysql/ndbdata

#vim /etc/my.cnf

[mysqld]

datadir=/usr/local/mysql/ndbdata

ndbcluster

ndb-connectstring=172.16.70.76

[mysql_cluster]

ndb-connectstring=172.16.70.76

2个sql节点配置:

#vim /etc/my.cnf

[mysqld]

ndbcluster

ndb-connectstring=172.16.70.76

[mysql_cluster]

ndb-connectstring=172.16.70.76

4个节点全都要改权限:

#chown mysql.mysql /etc/my.cnf

2个sql节点初始化：

[root@sql1_20 ~]# cd /usr/local/mysql/

[root@sql1_20 mysql]#  ./scripts/mysql_install_db --user=mysql

7. 按顺序启动各个节点：

启动管理节点：

[root@manager ~]# cd /usr/local/mysql/

[root@manager mysql]# ./bin/ndb_mgmd  -f cluster-conf/config.ini

启动data节点：

# ./bin/ndbd

启动sql节点：

#./bin/mysqld_safe --user=mysql &A

                                           mycat

MyCat M-M-S-S 环境

一、部署 mycat

[root@mycat ~]# tar xf jdk-8u91-linux-x64.tar.gz -C /usr/local/
[root@mycat ~]# ln -s /usr/local/jdk1.8.0_91/ /usr/local/java

[root@mycat ~]# tail -3 /etc/profile
JAVA_HOME=/usr/local/java
PATH=$JAVA_HOME/bin:$PATH
export JAVA_HOME PATH

[root@mycat ~]# source /etc/profile
[root@mycat ~]# env |grep JAVA
JAVA_HOME=/usr/local/java

二、配置Mycat

server.xml mycat/conf/server.xml 用来配置客户端如何链接mycat：user pass

 80         <user name="root">
 81                 <property name="password">123密码</property>
 82                 <property name="schemas">centos</property> 名字可以写多个用逗号隔开

配置schema名称
schema.xml /mycat/conf/schema.xml

  5         <schema name="centos" checkSQLschema="false" sqlMaxLimit="100">
                    //原来的所有全部删掉   只对库生效
  6         </schema>
balance="0" 配置读写分离

1222.png

balance 属性
负载均衡类型,目前的取值有 3 种:

1. balance="0", 不开启读写分离机制,所有读操作都发送到当前可用的 writeHost 上。

2. balance="1", 全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且 M1 与 M2互为主备),正常情况下,M2,S1,S2 都参与 select 语句的负载均衡。

3.balance="2", 所有读操作都随机的在 writeHost、readhost 上分发。

4.balance="3", 所有读请求随机的分发到 wiriterHost 对应的 readhost 执行,writerHost 不负担读压力,注意 balance=3 只在 1.4 及其以后版本有,1.3 没有。

writeType 属性
负载均衡类型,目前的取值有 3 种:

1. writeType="0", 所有写操作发送到配置的第一个 writeHost,第一个挂了切到还生存的第二个writeHost,重新启动后已切换后的为准.

Ｍ-M-S-S　准备Mycat连接的用户及权限　
192.168.122.234　为Mycat主机IP

2222.PNG