MySQL数据库(未完)

MySQL数据库

数据库简单分为两种和原理

MySQL(甲骨文公司收购):免费,开源成本低,体积小,多线程,多用户,性能卓越,服务稳定
Oracle(甲骨文公司产品):收费,安全,完善,操作复杂,有人维护

什么叫关系型数据库?
答:SQL数据库,数据和数据之间是有关系的,通过关系能将一系列数据提取出来

数据库分为两种

数据库简单的分为两种：
1.关系型数据库 --->MySQL和Orale
2.非关系型数据库 --->Memcached和Redis
什么叫做关系型数据库？
数据和数据之间是有关系的,通过关系能够将一系列数据都提取出来
什么是非关系型数据库?
Not only SQL

MySQL和Oracle区别

image_1cq3b4hrm1826cceios1ut91ctk1p.png-276.5kB

MySQL=MariaDB

MySQL=MariaDB它和MySQL是几乎一样的,是为了防止MySQL进行闭源操作

image_1cq3bage241f318bd4tufpve3m.png-193kB

postgresql和mongdb数据库

postgresql数据库
mongdb数据库(支持数据吞吐量很大)

B/S与C/S架构模式区别

B/S==>Web/server:用户通过Web浏览器打开输入域名就能访问服务器server方式叫做B/S架构,用户不需要安装任何东西

C/S==>Client/server:所谓客户端Client在用户的电脑里是需要下载并安装的,叫做C/S架构

MySQL安装过程

准备工作

rpm -qa | grep mysql 看看是否有mysql-server端
若存在用rpm -e mysql-server mysql --nodeps卸载

image_1cq3ar46r1dcc1b13ba1hn71q0i9.png-8.3kB

安装ncurses-devel和cmake包(cmake包需要下载)

yum -y install ncurses-devel
tar xf cmake-2.8.6.tar.gz -C /usr/src/
cd /usr/src/cmake-2.8.6/
./configure && gmake && gmake install(gmake编译需要安装gcc gcc-c++ make)

image_1cq3lilrg12m76m61iq1qdoer99.png-75.9kB

mysql源码编译及安装(包需要下载)

useradd -M -s /sbin/nologin mysql
tar xf mysql-5.5.22.tar.gz -C /usr/src
cd /usr/src/mysql-5.5.22
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DSYSCONFDIR=/etc -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all && make && make install
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql 主程序安装目录
-DSYSCONFDIR=/etc 配置文件存放目录
-DDEFAULT_CHARSET=utf8 默认字符集为utf8
-DDEFAULT_COLLATION=utf8_general_ci 默认字符集校对规则
-DWITH_EXTRA_CHARSETS=all 安装所有字符集

image_1cq3mi11n8g83me1ef1q601dd2m.png-45.1kB

/usr/local/mysql目录下

data数据目录
bin命令目录
support-files提供了很多模板,比如启动脚本和配置文件
scripts脚本目录

安装后优化操作

chown -R mysql:root /usr/local/mysql
/bin/cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf

image_1cq3mvl4616pptg917o0sjpsdr13.png-27.5kB

配置文件相关介绍

my-huge.cnf 巨大数据量
my-innodb-heavy-4G.cnf innodb引擎
my-large.cnf 大数据量
my-medium.cnf 测试使用
my-small.cnf 小数据量

MySQL数据库存储引擎

myisam
innodb
不同的引擎备份方法不同,在备份时一定要先看引擎在备份

MySQL添加系统服务

/bin/cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
chkconfig mysqld --add
chkconfig mysqld --list

image_1cq3n5mau1jlnc1vbfg1c6j1vtq1g.png-22kB

添加mysql命令执行的路径到PATH环境变量(让系统有mysql命令)

两种方法
echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
ln -s /usr/local/mysql/bin/* /usr/local/bin

image_1cq3plu3uvl112711fvh1it313a52a.png-14.2kB

执行脚本初始化数据库

/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
--user=mysql 指定用户身份
--basedir=/usr/local/mysql 指定安装目录
--datadir=/usr/local/mysql/data 指定数据目录也可以指定别的地方
只要2个ok就代表成功

image_1cq3pp34a17plobd1ll21pmmdbe2n.png-50.1kB

启动MySQL服务,并查看运行状态

/etc/init.d/mysqld start
netstat -antup | grep 3306

image_1cq3sahpu1fn21ltcbmp1ckjg3r9.png-20.1kB

连接并登陆到mysql操作环境

mysql
mysql -u 指定用户名
mysql -p 指定密码(不写默认没密码)
mysql -h 指定主机(不写默认登陆本地)
mysql -P 指定端口(不写默认3306端口)

image_1cq3sc1551mo283ar1r1b24bcm.png-33.7kB

设置数据库用户名密码

mysqladmin -uroot password 'linyaonie'
mysql -uroot -plinyaonie

image_1cq3sffd614rqvsjsc14ut36o13.png-47.3kB

如何让MySQL支持tab补全和简体中文

tab补全
vim /etc/my.cnf
[mysql]
#no-auto-rehash
auto-rehash
简体中文
vim /etc/my.cnf
[client]
default-character-set=utf8
修改完后重新启动服务
/etc/init.d/mysqld restart

image_1cq3sihif9nc13m31d3urunk3o1g.png-5kB
image_1cq3slbb619sc1cs91i7m1jon1gso1t.png-5kB

SQL语句概述

对表里数据进行操作用的一般都是开发用,运维只需要掌握简单的4中即可,增删改查

SQL语句概述.png-49.5kB

MySQL数据库表的管理操作

看数据库

show databases; 看小库,小库其实就是data目录下的子目录
use mysql; 对象设定成mysql
show tables; 设定完对象后看所有表

image_1cq4tb3d11m701v3i9oj120b1hv7p.png-32.1kB

创建和删除数据库

创建 create database yunjisuan;

删除 drop database yunjisuan;

image_1cq50r5al1350fsdidk1t6jfqu6e.png-44.7kB

创建和删除数据表

use yunjisuan;
创建 create table uses(user_name char(20) not null,user_passwd char(30) default '',primary key(user_name));
在yunjisuan库里创建名为uses的表,名字为20字节,密码为30字节,索引user_name也就是主见
查看表的字段信息,表头,不是数据 describe uses或desc uses

删除 drop table uses;

image_1cq524k3ik4okc1pq61pct7c49.png-41.9kB

四种对表数据进行操作的SQL语句

增

insert into 表明[字段名] values 具体数值
insert into uses values ('xiaohong','666666');
不指定字段名默认插入所有字段,有几个字段给插入几个值
insert into uses (user_name) values ('baibai');
指定字段默认插入一个值就可以了

image_1cq55iu1e1f9j1he5kp01t4mudv1f.png-25.7kB

删

delete from 表明 where 条件
delete from uses;
默认删除所有表信息
delete from uses where user_name='baibai';
指定删除某一字段所有信息

image_1cq566rg51t8vi9m1goi1kev18vac4.png-36.8kB

改

update 表名 set 字段='数值'
update uses set user_passwd='888888' where user_name='baibai';
where指定哪个字段修改,不指定默认所有字段

image_1cq55r08oo2gkd01r9vgc636p12.png-24.6kB

查

select 字段名 from 表名
select * from uses;
select * from yunjisuan.uses;
通过空间名和表名可以跳过对象查看表
select user,host,password from mysql.user;
mysql账号系统位置

image_1cq561atk104g1anjk1lqhr8upan.png-44.7kB

MySQL刷新语句

flush privileges;

MySQL修改密码

update mysql.user set password=password(`666666`) where host='localhost';
修改跟账号有关的信息是必须刷新flush privileges;

用户权限设置grand

设置用户权限

grant all on . to 'root'@'192.168.200.66' IDENTIFIED BY 'linyaonie';
all指的是所有的权限也就是类型,读权限select,删权限delete,改权限update,写权限insert
第一个所有的库,第二个所有的表
to是对哪个账号
IDENTIFIED BY是密码

登陆格式

mysql -uyunjisuan -p123123 -h 192.168.200.69；
mysql登陆格式

查看用户权限格式

show grants;
查看当前用户权限

show grants for ''@'192.168.200.69';
for查看指定用户的权限

select user();
查看当前登陆状态

select user,host,password from mysql.user;
查看mysql库账号密码

单独权限格式

GRANT select ON imployee_salary.* TO 'amber'@'localhost' IDENTIFIED BY 'linyaonie';

撤销用户权限

grant all on mysql.user to 'xxx'@'192.168.200.113' identified by 'linyaonie'
创建用户权限格式

revoke select on mysql.user from 'xxx'@'192.168.200.113';
撤销用户权限格式

show grants;
查看当前用户权限

show grants for 'xxx'@'192.168.200.113';
for查看指定用户的权限

设置完用户权限刷新

flush privileges;

MySQL通配符

_：任意单个字符
%:任意长度的任意字符

MySQL数据备份

数据库分完全备份和增量备份
完全备份为两种,通过命令和给目录打包,目录打包必须是mysql服务没运行状态

全备格式

which mysqldump
mysql全备命令,使用这个命令备份,用户是没办法写入数据,写库名备份的是表
mysqldump -uroot -plinyaonie yunjisuan > /tmp/yunjisuan-$(date +%F).sql
全备格式单个库
mysqldump -uroot -plinyaonie --databases yunjisuan benet > /tmp/yunjisuan_benet-$(date +%F).sql
mysqldump -uroot -plinyaonie --opt --all-databases | gzip -9 > /tmp/all-$(date +%F).sql.gz
全备格式多个库
--databases这个参数加上才知道后面的都是库而不是表
mysqldump -uroot -plinyaonie --opt --all-databases > /tmp/all-$(date +%F).sql
--opt是加速备份参数
--skip-add-locks全备不锁表参数
--all-databases是对所有库备份
mysqldump -uroot -plinyaonie yunjisuan uses > /tmp/uses-$(date +%F).sql
单表备份:指的是yunjisuan库里得uses表备份

恢复备份格式

source 备份的路径
交互备份格式

mysql -uroot -plinyaonie < 备份的路径
非交互备份格式

免登陆查询数据库格式

mysql -uroot -plinyaonie -e 'show databases;'

mysql -uroot -plinyaonie -e 'select * from yunjisuan.uses'

增备格式

MySQL没有提供直接增量备份的办法,可以通过MySQL提供的二进制日志(binary logs)简介实现增量备份。
MySQL二进制日志对备份的意义(mysqlbinlog)
二进制日志保存了所有更新或者可能更新数据库的操作
vim /etc/my.cnf
52行 max_binlog_size = 1024000
二进制日志默认大小1M,可修改
vim /etc/my.cnf
[mysql]
51行 log-bin=mysql-bin
mysql-bin可修改
114行 #log-bin=mysql-bin
修改完配置文件需要重启mysqld服务

读取二进制日志命令

which mysqlbinlog
mysql读取二进制日志命令

刷新日志命令

flush logs;
mysql刷新日志命令

查询位置写到哪

show master status;
查询当前位置写到哪里

恢复格式

mysqlbinlog /usr/local/mysql/data/mysql-bin.000001 | mysql -uroot -plinyaonie
一般恢复格式

mysqlbinlog --start-position='863' --stop-position='1039' /usr/local/mysql/data/mysql-bin.000001 | mysql -uroot -plinyaonie
按位置恢复格式

image_1cql3i2351kha1ps5mb5hood785a.png-177.6kB

数据备份的重要性

image_1cql3k8q5g7k68gv9c7hrgeap.png-62.9kB

数据备份的注意事项

image_1cql3ljlsc5hqr9c7h2ord8h16.png-63.6kB

企业备份策略的应用

在企业中如何去应用MySQL的备份策略?

image_1cql3602kar31u48j6dkjm1hulg.png-351.2kB

中小企业场景,数据库的全量备份如何实现?

image_1cql36vj2ef7b476vg1bb01hnf1d.png-224.6kB

中大型或大型企业,MySQL备份策略如何实现?

第一种成本非常高
第二种成本非常低

image_1cql38fgek6h2gk1omjset16as40.png-232.3kB

数据库常用的架构解析?

MySQL只写入,任何有压力的操作都不在主服务器
定时任务都在从库上实现
主库的从库是不能写东西,只能读,从库需要做静读操作

MySQL日志管理

日志包括:(1)错误日志,(2)通用查询日志,(3)二进制日志,(4)慢速查询日志
慢速查询日志:记录所有执行时间超过long_query_time秒的sql语句,可用于找到执行时间长的查询,以用于优化,默认未开启
###慢速查询日志开启路径
vim /etc/my.cnf
[mysqld]
long_query_time = 5 -->单位秒
log-slow-queries = mysql_slow.log -->日志的名字
改完重启服务即可
/etc/init.d/mysqld restart
ll /usr/local/mysql/data/mysql_slow.log -->慢速查询日志的绝对路径

事务的操作

默认情况下 MySQL 的事务是自动提交的，当 sql 语句提交时事务便自动提交。
手动对事务进行控制的方法：
·事务处理命令控制
·使用 set 设置事务处理方式
事务处理命令控制事务
begin 开始一个事务
commit 提交一个事务
rollback 回滚一个事务（撤销）
set autocommit = 0; --->//禁止自动提交
set autocommit = 1; ---> //开启自动提交
show variables like '%autocommit%';
查看MySQL全局变量

企业多实例应用

MySQL多实例。.png-155kB

多实例优点

(1)有效利用服务器资源
当单个服务器资源有剩余时,可以充分利用剩余的资源提供更多的服务,且可以实现资源的逻辑隔离

(2)节约服务器资源
当公司资金紧张,但是数据库又需要各自尽量独立的提供服务,而且需要主从复制等技术时,多实例就再好不过了

多实例缺点

多实例的弊端。.png-78.1kB

耦合度

一个配置文件，不好管理。工作开发和运维的统一原则为降低耦合度。

多配置文件，多启动程序部署方案

搭建MySQL多实例流程

安装MySQL需要的依赖包

yum -y install ncurses-devel libaio-devel

rpm -qa ncurses-devel libaio-devel

安装cmake源码包(源码包需要下载)

tar xf cmake-2.8.6.tar.gz -C /usr/src/
cd /usr/src/cmake-2.8.6/
./configure
gmake && gmake install

mysql源码编译及安装(包需要下载)

大型公司一般都会将MySQL软件定制成rpm包，然后放到yum仓库里，使用yum安装，中小企业里的二进制和编译安装的区别不大。

useradd -s /sbin/nologin -M mysql
id mysql
tar xf mysql-5.5.22.tar.gz -C /usr/src/
cd /usr/src/mysql-5.5.22/
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.5.22 -DMYSQL_DATADIR=/usr/local/mysql-5.5.22/data -DMYSQL_UNIX_ADDR=/usr/local/mysql-5.5.22/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii -DENABLED_LOCAL_INFILE=ON -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITHOUT_PARTITION_STORAGE_ENGINE=1 -DWITH_FAST_MUTEXES=1 -DWITH_ZLIB=bundled -DENABLED_LOCAL_INFILE=1 -DWITH_READLINE=1 -DWITH_EMBEDDED_SERVER=1 -DWITH_DEBUG=0
make && make install
ln -s /usr/local/mysql-5.5.22 /usr/local/mysql
ls /usr/local/mysql
查看/usr/local/mysql目录下有内容,则MySQL5.5.22源代码包采用cmake方式的安装就算成功了
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.5.22 \
> -DMYSQL_DATADIR=/usr/local/mysql-5.5.22/data \ #数据存放目录
> -DMYSQL_UNIX_ADDR=/usr/local/mysql-5.5.22/tmp/mysql.sock \ #MySQL进程间通信的套接字位置
> -DDEFAULT_CHARSET=utf8 \ #默认字符集为utf8
> -DDEFAULT_COLLATION=utf8_general_ci \ #默认字符集排序规则
> -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \ #额外的字符集支持
> -DENABLED_LOCAL_INFILE=ON \ #是否启用加载本地数据
> -DWITH_INNOBASE_STORAGE_ENGINE=1 \ #静态编译innodb存储引擎到数据库
> -DWITH_FEDERATED_STORAGE_ENGINE=1 \ #静态编译FEDERATED存储引擎到数据库
> -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ #静态编译blackhole存储引擎到数据库
> -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \ #不编译EXAMPLE存储引擎到数据库
> -DWITHOUT_PARTITION_STORAGE_ENGINE=1 \ #不支持数据库分区
> -DWITH_FAST_MUTEXES=1 \
> -DWITH_ZLIB=bundled \ #zlib压缩模式
> -DENABLED_LOCAL_INFILE=1 \ #是否启用本地的LOCAL_INFILE
> -DWITH_READLINE=1 \ #使用捆绑的readline
> -DWITH_EMBEDDED_SERVER=1 \ #是否要建立嵌入式服务器
> -DWITH_DEBUG=0 #禁用DEBUG（开启影响性能）

创建多实例数据文件目录

mkdir -p /data/{3306,3307}/data
cat tree /data/ --->tree命令需要yum安装才有
/data/
├── 3306 #3306实例目录
│ └── data #3306实例的数据文件目录
├── 3307 #3307实例目录
└── data #3307实例的数据文件目录

创建多实例配置文件

vim /data/3306/my.cnf

vim /data/3307/my.cnf

[client]
port = 3306
socket = /data/3306/mysql.sock
[mysqld]
user = mysql
port = 3306
socket = /data/3306/mysql.sock
basedir = /usr/local/mysql
datadir = /data/3306/data
open_files_limit = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_open_cache = 614
external-locking = FALSE
max_allowed_packet = 8M
#binlog_cache_size = 1M
#max_heap_table_size = 64M
#read_buffer_size = 2M
#read_rnd_buffer_size = 16M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 2M
query_cache_limit = 1M
query_cache_min_res_unit = 2k
#ft_min_word_len = 4
#default-storage-engine = MYISAM
thread_stack = 192K
transaction_isolation = READ-COMMITTED
tmp_table_size = 2M
max_heap_table_size = 2M
#log-bin=mysql-bin
#binlog_format=mixed
#slow_query_log
long_query_time = 1
pid-file = /data/3306/mysql.pid
relay-log = /data/3306/relay-bin
relay-log-info-file = /data/3306/relay-log.info
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062
replicate-ignore-db = mysql
server-id = 1
#key_buffer_size = 32M
#bulk_insert_buffer_size = 64M
#myisam_sort_buffer_size = 128M
#myisam_max_sort_file_size = 10G
#myisam_repair_threads = 1
#myisam_recover
innodb_additional_mem_pool_size = 4M
innodb_buffer_pool_size = 32M
innodb_data_file_path = ibdata1:128M:autoextend
innodb_file_io_threads = 4
#innodb_write_io_threads = 8
#innodb_read_io_threads = 8
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 4M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
[mysqldump]
quick
max_allowed_packet = 2M
[mysql]
no-auto-rehash
#[myisamchk]
#key_buffer_size = 512M
#sort_buffer_size = 512M
#read_buffer = 8M
#write_buffer = 8M
#[mysqlhotcopy]
#interactive-timeout
[mysqld_safe]
log-error = /data/3306/mysql_yunjisuan3306.err
pid-file = /data/3306/mysqld.pid
实例3307的配置文件只需要将3306配置文件里的所有3306数字替换成3307(server-id换个数字)即可。
配置完文件,多实例根/data目录结果如下
tree /data
/data
├── 3306
│ ├── data
│ └── my.cnf #这个就是3306实例的配置文件
└── 3307
├── data
└── my.cnf #这个就是3307实例的配置文件
###数据配置文件详解
socket = /data/3306/mysql.sock --->是MySQL的实例文件,假如强杀了mysql进程,需要删除.pid和.sock才能重启服务
user = mysql --->用户
port = 3306 --->端口
basedir = /usr/local/mysql --->安装目录
datadir = /data/3306/data --->数据目录
open_files_limit = 1024 --->最多的打开文件数量
back_log = 600 --->日志
max_connections = 800 --->最大的连接数量,mysql默认的连接数只有100,搭建完mysql需要把连接数调高
max_connect_errors = 3000 --->最大的错误连接
table_open_cache = 614 --->打开表以后的临时缓存
max_allowed_packet = 8M --->最大的sql语句不能超过8M,一条命令
sort_buffer_size = 1M --->缓冲区大小
join_buffer_size = 1M --->缓冲区大小
thread_cache_size = 100 --->线程缓存大小
query_cache_size = 2M --->sql语句的缓存大小
query_cache_limit = 1M --->sql语句缓存极限
tmp_table_size = 2M --->临时表的缓存大小
long_query_time = 1 --->sql语句执行不能超过1秒
pid-file = /data/3306/mysql.pid --->pid号的位置
binlog_cache_size = 1M --->增量二进制日志缓存大小
log-error = /data/3306/mysql_yunjisuan3306.err --->错误日志位置

创建多实例启动服务文件

vim /data/3306/mysql

vim /data/3307/mysql

#!/bin/bash
###############################################
#this scripts is created by Mr.chen at 2016-06-25
port=3306
mysql_user="root"
mysql_pwd=""
CmdPath="/usr/local/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
#startup function
function_start_mysql(){
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL....\n"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 >/dev/null &
else
printf "MySQL is running...\n"
exit
fi
}
#stop function
function_stop_mysql(){
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...\n"
exit
else
printf "Stoping MySQL...\n"
${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
fi
}
#restart function
function_restart_mysql(){
printf "Restarting MySQL...\n"
function_stop_mysql
sleep 2
function_start_mysql
}
case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
printf "Usage: /data/${port}/mysql{start|stop|restart}\n"
esac
3307实例的启动文件只需修改3306启动文件的端口即可
配置完文件,多实例根/data目录结果如下
tree /data
/data
├── 3306
│ ├── data
│ ├── my.cnf #3306实例的配置文件
│ └── mysql #3306实例的启动文件
└── 3307
├── data
├── my.cnf #3307实例的配置文件
└── mysql #3307实例的启动文件
###启动服务配置文件详解
port=3306 --->监听端口
mysql_user="root" --->用户
mysql_pwd="" --->这里需要修改为用户的实际密码

配置多实例的文件权限

通过下面的命令，授权mysql用户和组管理整个多实例的根目录/data
chown -R mysql.mysql /data
find /data -name "mysql" | xargs ls -l
通过下面的命令，授权MySQL多实例所有启动文件的mysql可执行，设置700权限最佳，注意不要用755权限，因为启动文件里有数据库管理员密码，会被读取到。
find /data -name "mysql" | xargs chmod 700
find /data -name "mysql" | xargs ls -l

初始化多实例的数据库文件

cd /usr/local/mysql/scripts
./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/3306/data --user=mysql
./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/3307/data --user=mysql
提示:
--basedir=/usr/local/mysql为MySQL的安装路径，--datadir为不同的实例数据目录

启动多实例的服务

/data/3306/mysql start

/data/3307/mysql start

netstat -antup | grep 330
查询端口是否开启成功

登陆MySQL测试

mysql -S /data/3306/mysql.sock

mysql -uroot -plinyaonie -S /data/3306/mysql.sock

-S /data/3306/mysql.sock,用于区别登陆不同的实例

多实例设置密码

mysqladmin -u root -S /data/3306/mysql.sock password 'linyaonie'
设置另一个实例只需要-S指定另一个实例就可以了

再增加一个多实例的办法

mkdir -p /data/3308/data
\cp /data/3306/my.cnf /data/3308/
\cp /data/3306/mysql /data/3308/
sed -i 's#3306#3308#g' /data/3308/my.cnf
sed -i 's#server-id = 1#server-id = 8#g' /data/3308/my.cnf
sed -i 's#3306#3308#g' /data/3308/mysql
chown -R mysql:mysql /data/3308
chmod 700 /data/3308/mysql
cd /usr/local/mysql/scripts
./mysql_install_db --datadir=/data/3308/data --basedir=/usr/local/mysql --user=mysql
chown -R mysql:mysql /data/3308
egrep "server-id|log-bin" /data/3308/my.cnf
/data/3308/mysql start
netstat -antup | grep 3308
提示：最好把server-id按照IP地址最后一个小数点的数字设置
成功标志：多了一个启动的端口3308
如果配置以后，服务启动后却没有运行起来，别忘了一定要看MySQL错误日志，在/data/3308/my.cnf最下面有错误日志路径地址。

重点提示

禁止使用pkill，kill -9，killall -9等命令强制杀死数据库，这会引起数据库无法启动等故障的发生。

MySQL主从复制介绍

主从复制介绍。.png-49.5kB

概述

概述。.png-41.9kB

主从复制企业应用场景

MySQL主从复制集群功能使得MySQL数据库支持大规模高并发读写成为可能，同时有效地保护了物理服务器宕机场景的数据备份。

从服务器作为主服务器的实时数据备份

从服务器作为主服务器的实时数据备份。.png-46.3kB

主从服务器实现读写分离，从服务器实现负载均衡

主从服务器实现读写分离，从服务器实现负载均衡。.png-59.3kB
读写分离图解。.png-204.3kB

实现MySQL主从读写分离的方案

（1）通过程序实现读写分离（性能和效率最佳，推荐）

PHP和Java程序都可以通过设置多个连接文件轻松地实现对数据库的读写分离，即当语句关键字为select时，就去连接读库的连接文件，若为update，insert，delete时，则连接写库的连接文件。
通过程序实现读写分离的缺点就是需要开发人员对程序进行改造，使其对下层不透明，但这种方式更容易开发和实现，适合互联网业务场景。

根据业务重要性拆分从库方案

根据业务重要性拆分从库方案。.png-242.8kB

（2）通过开源的软件实现读写分离

MySQL-proxy，Amoeba等代理软件也可以实现读写分离功能，这些软件的稳定性和功能一般，不建议生产使用。绝大多数公司常用的还是在应用端发程序实现读写分离。

（3）大型门户独立开发DAL层综合软件

百度，阿里等大型门户都有开发牛人，会花大力气开发适合自己业务的读写分离，负载均衡，监控报警，自动扩容，自动收缩等一系列功能的DAL层软件。

image_1cqukva691uuh1f381s3479r1srs9.png-168.2kB

主从复制都是异步复制

在当前的生产工作中，MySQL主从复制都是异步的复制方式，既不是严格实时的数据同步，但是正常情况下给用户的体验是真实的。

主从复制搭建流程

主从复制注意事项

从库禁止写数据，从库权限设置只读，否则容易出问题

主从复制数据必须是一样的,所以需要先给主库之前的数据做一个全备,在从binlog当前所处位置复制

mysqldump -uroot -p密码 --all-databases -S 多实例主库路径 > /tmp/mysql_主库名_all$(date +%F)

主库(Master)

开启binlog日志和server-id

[mysqld]
server-id = 1 #用于同步的每台机器或实例server-id都不能相同
log-bin = /data/3306/mysql-bin #binlog日志的位置
修改my.cnf配置后,需要重启动数据库,命令为：/data/3306/mysql restart,注意要确认真正重启了。

image_1cquvq4101lv91gldthatsq1porg5.png-68.4kB

image_1cqv08rl813ljk92juu6tm1ghcjk.png-13.4kB

登陆数据库，检查参数的更改情况

mysql -uroot -p123123 -S /data/3306/mysql.sock
登陆3306实例
show variables like 'log_bin';
binlog功能已开启(on开启状态)
show variables like 'server_id';
查看MySQL的系统变量（like类似于grep过滤,数值1）

image_1cqv02b9o1rvq14k222fl211v1miq.png-56.2kB

创建主从复制账号及密码并查询授权

mysql -uroot -plinyaonie -S /data/3306/mysql.sock
登陆用户
grant replication slave on *.* to 'yunjisuan'@'192.168.200.%' identified by 'linyaonie';
创建主从复制账号,主从复制必须这个权限:REPLICATION SLAVE
flush privileges;
创建完刷新
select user,host from mysql.user;
检查主库创建的主从复制账号
show grants for yunjisuan@'192.168.0.%';
查看账号的授权情况

image_1cqv0df83eiu1kl6fef1aak1iookh.png-40.5kB

image_1cqv5h8pudu81ambeo0127t6nh9.png-63kB

主库全备并查询日志位置

mysqldump -plinyaonie -S /data/3306/mysql.sock --all-databases > /tmp/all.sql
主库全备
show master status;
在主库查询日志名和写到哪里

image_1cqv760dm13621tpg1gu71jc3lh54o.png-23kB

把主库的全备恢复到从库上

mysqldump -plinyaonie -S /data/3307/mysql.sock < /tmp/all.sql
把主库的全备恢复到从库上

image_1cqv79g18o3dme512pcd071cdp55.png-9.9kB

从库(Slave)

关闭binlog日志,开启中继日志

[mysqld]
relay-log = /data/3307/relay-bin
server-id = 3
调整等号后的数值，和任何一个数据库实例都不同

image_1cqv7gcfk8jbi5f7la1f8ah995i.png-62.6kB

配置文件完成后重启服务

/data/3307/mysqld restart

image_1cqv7lsfuntg17ccmn91sbl7dmb1.png-18.5kB

登陆用户,告诉从库,主库的信息

mysql -uroot -plinyaonie -S /data/3307/mysql.sock
登陆用户
reset slave all;
清除主从复制授权信息
change master to master_host='192.168.200.75',master_port=3306,master_user='yunjisuan',master_password='linyaonie',master_log_file='mysql-bin.000001',master_log_pos=107;
告诉从库：主库IP地址和端口号、主从复制账号及密码、当前binlog日志名及在当前binlog日志中所处的位置

image_1cqv8lhnj1p9f1eo9mao1rdibbtbe.png-62.6kB

开启主从复制命令

start slave;
开启主从复制
show slave status\G
查看授权信息

image_1cqv8rq6h11601hbk1umausb18p7fb.png-44kB

主从复制测试

image_1cqv8vgrrh85c6l14nh5ntdp3fo.png-31.8kB

image_1cqv8vtq01sflnn1139u19cpu4g5.png-56.4kB

主从复制原理详解

(1) 在Slave服务器上执行start slave命令开启主从复制开关，开始进行主从复制
(2）此时，Slave服务器的I/O线程会通过在Master上已经授权的复制用户权限请求连接Master服务器
并请求从指定binlog日志文件的指定位置（日志文件名和位置就是在配置主从复制服务时执行change master命令指定的）之后开始发送binlog日志内容。
(3）Master服务器接收到来自Slave服务器的I/O线程的请求后
其上负责复制的I/O线程会根据Slave服务器的I/O线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息
然后返回给Slave端的I/O线程。返回的信息中除了binlog日志内容外，还有在Master服务器端记录的新的binlog文件名称，以及在新的binlog中的下一个指定更新位置。
(4）当Slave服务器的I/O线程获取到Master服务器上I/O线程发送的日志内容，日志文件及位置点后
会将binlog日志内容依次写到Slave端自身的Relay Log（即中继日志）文件（MySQL-relay-bin.xxxx）的最末端
并将新的binlog文件名和位置记录到master-info文件中
以便下一次读取Master端新binlog日志时能够告诉Master服务器从新binlog日志的指定文件及位置开始请求新的binlog日志内容。
(5）Slave服务器端的SQL线程会实时检测本地Relay Log中I/O线程新增加的日志内容
然后及时地把Relay Log文件中的内容解析成SQL语句
并在自身Slave服务器上按解析SQL语句的位置顺序执行应用这些SQL语句
并在relay-log.info中记录当前应用中继日志的文件名及位置点。

简洁描述主从复制原理

1、MySQL 从服务器开启 I/O 线程，向主服务器请求数据同步（获取二进制日志）
2、MySQL 主服务器开启 I/O 线程回应从服务器
3、从服务器得到主的二进制日志写入中继日志
4、从服务器开启 SQL 线程将日志内容执行，实现数据同步

MySQL各种问题综合阐述

工作中MySQL从库停止复制故障案例

模拟重现故障的能力是运维人员最重要的能力。下面就来次模拟操作。先在从库创建一个库，然后去主库创建同名的库来模拟数据冲突
Slave_SQL_Running: No
Last_Error: Error 'Can't create database 'yunjisuan'; database exists' on query. Default database: 'yunjisuan'. Query: 'create database yunjisuan'
方法一:可以人工手动在从库删除同名的库来恢复工作,删除同名库需要先关闭主从同步
stop slave;
drop database yunjisuan;
start slave;
方法二:关闭从同步,调动sql_slave指针
stop slave;
set global sql_slave_skip_counter=1;
start slave;
提示
Seconds_Behind_Master: 0 #0表示已经同步状态
set global sql_slave_skip_counter=n; #n取值>0,忽略执行N个更新。

MySQL主从复制延迟问题的原因及解决方案

问题一：主库的从库太多，导致复制延迟
例如，从库数量以3~5个为宜，要复制的从节点数量过多，会导致复制延迟。
问题二：从库硬件比主库差，导致复制延迟。
查看Master和Slave的系统配置，可能会因为机器配置不当，包括磁盘I/O，CPU，内存等各方面因素造成复制的延迟。这一般发生在高并发大数据量写入场景中。
问题三：慢SQL语句太多
假如一条SQL语句执行时间是20秒，那么从执行完毕到从库上能查到数据至少需要20秒，这样就延迟20秒了。
一般要把SQL语句的优化作为常规工作，不断的进行监控和优化，如果单个SQL的写入时间长，可以修改后分多次写入
通过查看慢查询日志或show full processlist命令，找出执行时间长的查询语句或大的事务。
问题四：主从复制的设计问题
例如，主从复制单线程，如果主库写并发太大，来不及传送到从库，就会导致延迟。
更高版本的MySQL可以支持多线程复制，门户网站则会自己开发多线程同步功能。
MySQL5.6版本是重大更新
问题五：主从库之间的网络延迟
主从库的网卡，网线，连接的交换机等网络设备都可能成为复制的瓶颈，导致复制延迟，另外，跨公网主从复制很容易导致主从复制延迟。
问题六：主库读写压力大，导致复制延迟。
主库硬件要搞好一点，架构的前端要加buffer及缓存层。

通过read-only参数让从库只读访问

read-only参数选项可以让从服务器只允许来自从服务器线程或具有SUPER权限的数据库用户进行更新，确保从服务器不接受来自用户端的非法用户更新

read-only参数允许数据库更新的条件为

(1) 具有SUPER权限的用户可以更新，不受read-only参数影响，例如：管理员root。

(2)来自从服务器线程可以更新，不受read-only参数影响，例如：yunjisuan用户。

(3)再生产环境中，可以在从库Slave中使用read-only参数，确保从库数据不被非法更新。

read-only参数的配置方法如下

在my.cnf里[mysqld]模块下加read-only参数重启数据库配置如下
[mysqld]
read-only

让MySQL从库记录binlog日志的方法

从库需要记录binlog的应用场景：当前的从库还要作为其他从库的主库，例如级联复制或双主互为主从场景的情况下。下面介绍从库记录binlog日志的方法。

log-slave-updates #必须要有这个参数
log-bin = /data/3307/mysql-bin
expire_logs_days = 7 #相当于find /data/3307/ -type f -name "mysql-bin.000*" -mtime +7 | xargs rm -f

如何给开发授权MySQL读写账号

第一种方法
一个账号分别有负责读(从库)和有负责写(主库)
好处--->开发利用写账号在Master写入,利用读账号在Slave里读取,账号无法混用,因此可以回避,开发像从库写入数据的风险
缺点1--->两个账号给开发,对于运维来说显得很不专业
缺点2--->主从数据库的账号系统不一致,我们的全备都是在从库做,因此备份的账号是从库的,这就会导致数据恢复起来,遇到麻烦
第二种方法
一个账号,权限也一样,在从库和主库上都能对数据库进行读写,如此一来,MySQL主从库账号系统是一致的
那么我们如何来规避从库被学入数据的风险呢?
将所有的MySQL从库的表权限设置为read only(只读,永久只读)
在my.cnf里[mysqld]模块下加read-only参数重启数据库配置如下
[mysqld]
read-only

实现对主数据库锁表只读

flush table with read lock;
锁表只读

unlock tables;
解锁表只读

对主数据库锁表只读（当前窗口不要关掉）的命令如下：
flush table with read lock;
提示：
在引擎不同的情况下，这个锁表命令的时间会受下面参数的控制。锁表时，如果超过设置时间不操作会自动解锁。
默认情况下自动解锁的时长参数值如下：
show variables like '%timeout%';
查询锁表时常
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 120 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| wait_timeout | 28800 |
+----------------------------+----------+
interactive_timeout | 28800 --->自动解锁时间受本参数影响
wait_timeout | 28800 --->自动解锁时间受本参数影响
锁表后查看主库状态,可通过当前binlog日志文件名和二进制binlog日志偏移量来查看，结果如下：
注意，show master status;命令显示的信息要记录在案，后面的从库导入全备后，继续和主库复制时就是要从这个位置开始。
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 345 | | |
+------------------+----------+--------------+------------------+
或者新开一个命令行窗口，用如下命令查看锁表后的主库binlog位置点信息：
mysql -uroot -p123123 -S /data/3306/mysql.sock -e "show master status"
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 533 | | |
+------------------+----------+--------------+------------------+
锁表后，一定要单开一个新的SSH窗口，导出数据库的所有数据，如果数据量很大（50GB以上），并且允许停机，可以停库直接打包数据文件进行迁移，那样更快。
[root@localhost ~]# mkdir -p /server/backup
[root@localhost ~]# mysqldump -uroot -p123123 -S /data/3306/mysql.sock --events -A -B | gzip >/server/backup/mysql_bak.$(date +%F).sql.gz
#注意：-A表示备份所有库；-B表示增加use DB和 drop 等（导库时会直接覆盖原有的）
[root@localhost ~]# ll /server/backup/mysql_bak.2017-07-21.sql.gz
-rw-r--r--. 1 root root 137344 Jul 21 10:17 /server/backup/mysql_bak.2017-07-21.sql.gz
#为了确保导出数据期间，数据库没有数据插入，导库完毕可以再次检查主库状态信息，结果如下：
[root@localhost ~]# mysql -uroot -p123123 -S /data/3306/mysql.sock -e "show master status"
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 533 | | |
+------------------+----------+--------------+------------------+
提示：若无特殊情况，binlog文件及位置点和锁表后导出数据前是一致的，即没有变化。
#导出数据完毕后，解锁主库，恢复可写，命令如下.因为主库还要对外提供服务，不能一直锁定不让用户访问。
mysql> unlock tables;
解锁表只读
Query OK, 0 rows affected (0.00 sec)

MySQL存储引擎

Mysql两种重要的存储引擎

MylSAM特点

MyISAM存储引擎是MySQL关系数据库系统5.5版本之前默认的存储引擎,前身是ISAM。

MyISAM引擎的数据表的特点
(1)不支持事务 --->不支持复杂功能,系统资源占用少
(2)读的速度非常快,读写互斥 --->读速度快,读的时候不能写,写的支持不好
(3)一旦写入数据,引擎直接对数据表进行表级别的锁定,其他用户不能再写也不能在读 --->写速度很慢
(4)可以缓存索引,但不能缓存数据 --->内存占用小,索引就相当于目录,数据相当于内容

InnoDB特点

InnoDB数据表的典型特点
(1)支持事务 --->支持负责功能,对服务器资源要求高
(2)一旦写入数据,行级别锁定,单表可以支持并发写 --->对写支持好
(3)可以缓存索引,也可以缓存数据 --->内存占用大,对服务器要求高

MySQL查看各引擎的命令

show engines;
查看默认引擎命令
show table status from yunjisuan where name='uses' \G;
查看表引擎命令
show table status from mysql where name='user' \G;
查看系统表引擎命令
create table id(id int) engine=myisam
创建表并归属哪个引擎

posted @ 2018-10-29 15:28 L1n 阅读(316) 评论(0) 编辑收藏举报

刷新页面返回顶部

L1n

若不是生活所迫，谁愿意把自己弄的一身才华。

MySQL数据库(未完)

MySQL数据库

数据库简单分为两种和原理

数据库分为两种

MySQL和Oracle区别

MySQL=MariaDB

postgresql和mongdb数据库

B/S与C/S架构模式区别

MySQL安装过程

准备工作

安装ncurses-devel和cmake包(cmake包需要下载)

mysql源码编译及安装(包需要下载)

/usr/local/mysql目录下

安装后优化操作

配置文件相关介绍

MySQL数据库存储引擎

MySQL添加系统服务

添加mysql命令执行的路径到PATH环境变量(让系统有mysql命令)

执行脚本初始化数据库

启动MySQL服务,并查看运行状态

连接并登陆到mysql操作环境

设置数据库用户名密码

如何让MySQL支持tab补全和简体中文

SQL语句概述

MySQL数据库表的管理操作

看数据库

创建和删除数据库

创建和删除数据表

四种对表数据进行操作的SQL语句

增

删

改

查

MySQL刷新语句

MySQL修改密码

用户权限设置grand

设置用户权限

登陆格式

查看用户权限格式

单独权限格式

撤销用户权限

设置完用户权限刷新

MySQL通配符

MySQL数据备份

全备格式

恢复备份格式

免登陆查询数据库格式

增备格式

读取二进制日志命令

刷新日志命令

查询位置写到哪

恢复格式

数据备份的重要性

数据备份的注意事项

企业备份策略的应用

在企业中如何去应用MySQL的备份策略?

中小企业场景,数据库的全量备份如何实现?

中大型或大型企业,MySQL备份策略如何实现?

数据库常用的架构解析?

MySQL日志管理

事务的操作

企业多实例应用

多实例优点

多实例缺点

耦合度

搭建MySQL多实例流程

安装MySQL需要的依赖包

安装cmake源码包(源码包需要下载)

mysql源码编译及安装(包需要下载)

创建多实例数据文件目录

创建多实例配置文件

创建多实例启动服务文件

配置多实例的文件权限

相关命令加入全局变量

初始化多实例的数据库文件

启动多实例的服务

登陆MySQL测试

多实例设置密码