Mysql服务部署

MySQL是一种关联数据库管理系统,关联数据库将数据保存在不同的表格中,而不是将所有数据放在一个大仓库内,这样就增加了速度关提高了灵活性。MySQL所使用的SQL语言是访问数据库最常用标准化语言。
常用关系型数据库软件有:MySQL、MariaDB、Oracle、SQL Server、PostgreSQL、DB2。
常用关系型数据库(RDBMS):是将数据组为相关的行和列的系统,特点如下:
数据以表格的形式出现;
每行记录数据的真实内容;
每列记录数据真实内容的数据域;
无数的行和列组成一张表;
若干的表组成一个数据库。
 
Mysql服务器安装
1、yum方式安装:
yuminstall mysql-server mysql-devel mysql-libs -y        CentOS 6.X版本
yuminstall mariadb-server mariadb mariadb-libs -y        CentOS 7.X版本
2、源码安装mariadb方法,通过cmake、make、make install三个步骤实现:
MariaDB5.5及更高版本使用cmake编译,所有得先安装cmake,这里使用编译安装cmake.
编译环境及依赖关系
yum groupinstall -yDevelopment Tools
yum -y installncurses-devel zlib-devel
获取cmake :
wget https://cmake.org/files/v3.13/cmake-3.13.0-rc1.tar.gz
tar xfcmake-3.13.0-rc1.tar.gz -C /usr/local/
编译安装:
cd/usr/local/cmake-3.13.0-rc1/
./configure
gmake&& gmake install
安装Mariadb:
wget http://mirrors.neusoft.edu.cn/mariadb//mariadb-5.5.61/source/mariadb-5.5.61.tar.gz
tar xfmariadb-5.5.61.tar.gz -C/usr/local/mysql
yum  install cmake  ncurses -devel ncurses -y
 cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
    -DSYSCONFDIR=/etc \
    -DDEFAULT_CHARSET=utf8 \
    -DDEFAULT_COLLATION=utf8_general_ci \
    -DWITH_EXTRA_CHARSETS=all \
      ......
3、MySQL源代码安装参数详解:
cmake .-DCMAKE_INSTALL_PREFIX=/usr/local/mysql                    cmake 预编译
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock                 MySQL socket 通信文件位置
-DMYSQL_DATADIR=/data/mysql                             MySQL 数据存放路径
-DSYSCONFDIR=/etc                                                配置文件路径
-DMYSQL_USER=mysql                                            MySQL运行用户
-DMYSQL_TCP_PORT=3306                                      监听端口
-DWITH_XTRADB_STORAGE_ENGINE=1                   开启XtraDB引擎支持
-DWITH_INNOBASE_STORAGE_ENGINE=1               开启InnoDB引擎支持
-DWITH_PARTITION_STORAGE_ENGINE=1               开启Partition引擎支持
-DWITH_BLACKHOLE_STORAGE_ENGINE=1             开启BlackHole引擎支持
-DWITH_MYISAM_STORAGE_ENGINE=1                   开启MyISAM引擎支持
-DWITH_READLINE=1                                               开启快捷键功能
-DENABLED_LOCAL_INFILE=1                                   允许从本地导入数据
-DWITH_EXTRA_CHARSETS=1                                   支持额外的字符集
-DDEFAULT_CHARSET=utf8                                      默认字符集UTF-8
-DDEFAULT_COLLATION=uft8_general_ci                 检验字符
-DEXTRA_CHARSETS=all                                           安装所有扩展字符集
-DWITH_BIG_TABLES=1                                            将临时表存储在磁盘上
-DWITH_DEBUG=0                                                    禁止调试模式支持
make                                                      编译
make  install                                            安装
4、将源码安装的MySQL数据库服务设置为系统服务,可以使用Chkconfig管理,并启动数据库。
cd/usr/local/mysql/
\cp support-files/my-large.cnf  /etc/my.cnf
\cp support-files/mysql.server   /etc/init.d/mysqld
chkconfig  --add mysqld
chkconfig--level 35 mysqld on
mkdir  -p /data/mysql
useraddmysql
/usr/local/mysql/scripts/mysql_install_db--user=mysql  --datadir=/data/mysql/  --basedir=/usr/local/mysql/
ln  -s /usr/local/mysql/bin/*   /usr/bin/
service  mysqld restart
ps -ef |grepmysqld
5、不设置为系统服务,也可以用源码启动方式:
cd  /usr/local/mysql
mkdir -p/data/mysql
useraddmysql
/usr/local/mysql/scripts/mysql_install_db--user=mysql --datadir=/data/mysql/ --basedir=/usr/local/mysql/
ln -s/usr/local/mysql/bin/*  /usr/bin/
/usr/local/mysql/bin/mysqld_safe  --user=mysql &
 
MySQL数据库必备命令操作
常用命令:
show databases;                          查看所有的数据库
create database tang;                创建名为tang的数据库
use tang;                                       进入tang数据库
show tables;                                 查看数据库里有多少张表
create tablet1 (id varchar(20),name varchar(20)) ;  创建名为t1表,并创建两个字段,id、name,varchar表示设置数据长度,用字符来定义长度单位,其中1汉字=2字符=2字节
insert intot1 values ("1","tang");                           向表中插入数据
select *from t1;                                                               查看t1表数据内容                                      
select * from t1 where id=1 and name='tang';                 id、name多条件查询
desct1;                                                                            查看t1表字段内容
alter table t1 modify column name varchar(30);               修改name字段的长度
update t1 set name='TANG' where id=1 ;                        修改name字段内容
flushprivileges;                                                               刷新权限
delete fromt1;                                                                 清空表内容
drop tablet1;                                                                   删除表
dropdatabase tang;                                                        删除tang数据库
showvariables like '%char%';                                          查看数据库字符集
showengines;                                                                  查看MySQL存储引擎
show variables like '%storage_engine%';                          查看MySQL默认的存储引擎
alter tablet1 engine=innodb;                                         修改t1表存储引擎为InnoDB
 
MySQL数据库字符集设置
MySQL数据库默认编码为latinl,存储中文字符时,显示或者web调用时会显示乱码,需修改MySQL默认字符集为UTF-8。有两种方法:
1、编辑vim/etc/my.cnf配置文件,在相应字段中加入相应的参数字符集,修改后重启MySQL服务即可。
[client]字段里加入:default-character-set=utf8
[mysqld]字段里加入:charactor-set-server=uft8
[mysql]字段里加入:default-charactor-set=uft8
2、MySQL命令行中运行如下命令:
showvariables like '%char%';
SETcharactor_set_client = utf8;
SETcharactor_set_results = utf8;
SETcharactor_set_connection = utf8;
 
MySQL数据库密码管理
1、MySQL创建用户及授权:
grant all ontang. * to tang@localhost identified by 'passwd';
授权locahost主机通过tang用户和passwd密码访问本地tang数据库的所有权限;
grantselect,insert,update,delete on *. * to tang@"%" identified by'passwd';
授权所有主机通过tang用户和passwd密码访问本地的tang库的查询,插入,更新,删除权限;
grant all ontang. * to tang@'xx.xx.xx.xx' identified by 'passwd';
授权xx.xx.xx.xx主机通过tang用户和passwd密码访问本地的tang库的所有权限;
2、数据库密码破解方法:
/etc/init.d/mysqldstop  或 systemctl stopmariadb.server                  先停止数据库服务
/usr/bin/mysqld_safe--user=mysql --skip-grant-tables &                跳过权限方式启动
usemysql                                                       用户及密码认证信息在mysql数据库的user表中
update userset passwd=passwd('123456') where user='root';         修改user表中root密码
修改完密码后需停止MySQL跳过权限表的启动进程,再正常启动MySQL,就可以用新密码登录了。
 
MySQL数据库配置文件详解
[mysqld]                                服务器端配置
datadir=/data/mysql/                        数据目录
socket=/var/lib/mysql/mysql.sock        socket通信设置
user=mysql                                使用MySQL用户启动
symbolic-links=0                        是否支持快捷方式
log-bin=mysql-bin                        开启bin-log日志
server-id=1                                      MySQL服务的ID
auto_increment_offset=1                自增长字段从固定数开始
auto_increment_increment=2                自增长字段每次递增的量
socket=/tmp/mysql.sock                        MySQL客户程序与服务器之间的本地难住套接字文件
port=3306                                指定MySQL监听的端口
key_buffer=384MB                        key_buffer是用于索引块的缓冲区大小
table_cache=512                        所有线程打开表的数量        
sort_buffer_size=2MB                        为每个需要进行排序的线程分配该大小的一个缓冲区
read_buffer_size=2MB                        读查询操作所能使用的缓冲区大小
query_cache_size=32MB                        指定MySQL查询结果缓冲区的大小
read_rnd_buffer_size=8MB                改参数在使用行指针指序之后,随机读
myisam_sort_buffer_size=64MB                MyISAM表发生变化时重新排序所需的缓冲
thread_concurrency=8                        最大并发线程数,取值为服务器逻辑CPU数量X2
thread_cache=8                                缓存可重用的线程数
skip-locking                                避免MySQL外部锁定,减少出错几率增强稳定性
default-storage-engine=INNODB        设置MySQL默认引擎为InnoDB
#mysqld_safeconfig                   MySQL服务安全配置
[mysqld_safe]                                MySQL服务安全启动配置
log-error=/var/log/mysqld.log                MySQL错误日志路径
pid-file=/var/run/mysqld/mysqld.pid        MySQLPID进程文件
key_buffer_size=2048MB                        MyISAM表索引缓冲区的大小
max_connections=3000                        MySQL最大连接数
innodb_buffer_pool_size=2048MB        InnoDB内存缓冲数据和索引大小
basedir=/usr/local/mysql/                数据库安装路径
[mysqldump]                                数据库导出段配置
max_allowed_packet=16MB                服务器和客户端发送的最大数据包
 
MySQL数据库索引案例
MySQL索引可以用来快速地寻找某些具有特定值的记录,所有MySQL索引都以B-树的形式保存。
MySQL数据库常见索引类型包括:
普通索引(normal)        使用最广泛
唯一索引(unique)        不允许有重复的索引,允许有空值
全文索引(fulltext)        只能用于MyISAM表,主要用于大量的内容检索
主键索引(primarykey)        又称为特殊的唯一索引,不允许有空值
组合索引                为提高MySQL效率可建立组合索引
MySQL数据库表创建各种个索引命令,以t1表为案例:
主键索引        ALTERTABLE t1 ADD PREMARY KEY('column');
唯一索引        ALTERTABLE t1 ADD UNIQUE('column');
普通索引        ALTERTABLE t1 ADD INDEX index_name('column');
全文索引        ALTERTABLE t1 ADD FULL TEXT('column');
组合索引        ALTERTABLE t1 ADD INDEXindex_name('column','column2','column3');        
MySQL数据库表删除各个索引命令,以t1表为案例:
DROP INDEX index_nameON t1;
ALTER TABLE t1 DROPINDEX index_name;
ALTER TABLE t1 DROPPRIMARY KEY;
MySQL数据库查看表索引,以t1表为案例:
show index from t1;
show keys from t1;
MySQL数据库索引的缺点:
1、MySQL数据库索引虽然能够提高数据库查询速度,但同时会降低更新、删除、插入表的速度,例如对表insert、update、delete时,update表MySQL不仅要保存数据,还需保存更新索引。
2、建立索引会占用磁盘空间,大表上创建了多种组合索引,索引文件就会占用大量的空间。
 
MySQL数据库慢查询
MySQL数据库慢查询主要用于跟踪异常的SQL语句,可以分析出当前程序里哪些SQL语句比较耗费资源,慢查询日志则用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL语名,会被记录到慢查询日志中。
查看数据库是否开启慢查询:
show variables like"%slow%";
show variables like"%long_query%";
MySQL慢查询参数详解:
log_show_queries                             关闭慢查询日志功能
long_query_time                        慢查询超时时间,默认为10s,MySQL5.5以上可以设置微秒
slow_query_log                        关闭慢查询日志
slow_query_log_file                慢查询日志文件
slow_launch_time                threadcreate时间,单位为秒,若thread create时间超过这个值,该变量slow_launch_time的值会加1
log-queries-not-using-indexes        记录未添加索引的SQL语句
开启MySQL慢查询日志方法有以下两种:
1、MySQL数据库命令行执行命令:
set globalslow_query_log=on;
show variables like"%slow%";
2、编辑my.cnf配置文件中添加代码:
log-slow-queries=/data/mysql/tang-slow.log
long_query_time=0.01
log-queries-not-using-indexes
慢查询功能开启之后,数据库会自动将执行时间超过设定时间的SQL语句添加至慢查询日志文件中,可以通过慢查询日志文件定位执行慢的SQL,从而对其优化,可以通过mysqldumpslow命令行工具分析日志。
命令mysqldumpslow参数详解:
s        排序
l        查询锁的总时间
r        返回记录数
t        查询总时间排序
al        平均锁定时间
ar        平均返回记录数
at        平均查询时间
c        计数
-tn        显示头n条记录
MySQL慢查询mysqldumpslow按照返回的行数从大到小,查看前2行:
mysqldumpslow -s r -t 2tang-slow.log
MySQL慢查询mysqldumpslow按照查询总时间从大到小,查看前5行:
mysqldumpslow -s t -t 5tang-slow.log
 
MySQL数据库优化
MySQL数据库优化三分靠配置文件及硬件资源的优化,七分靠SQL语句的优化。
MySQL数据库优化包括:配置文件的优化、SQL语句的优化、表结构的优化、索引的优化。
配置的优化包括:系统内核、配件资源、内存、CPU、MySQL本身配置文件的优化。
硬件的优化:一种是增加内存和提高磁盘读写速度,进而提高MySQL数据库的查询、更新的速度;
     另一种提高MySQL性能的方式是使用多块磁盘来存储数据,可以从多块磁盘上并行读取数据,进而提高读取数据的速度。
企业级MySQL百万量级真实环境配置文件my.cnf的内容:
[client]
port=3306
socket=/tmp/mysql.sock
[mysqld]
user=mysql
server-id=1
port=3306
socket=/tmp/mysql.sock
datadir=/data/mysql/
old_passwords=1
lower_case_table_names=1
character-set-server=utf8
default-storage-engine=MYISAM
log-bin=bin.log
log-errror=error.log
pid-file=mysql.pid
long_query_time=2
slow_query_log
slow_query_log_file=slow.log
binlog_cache_size=4MB
binlog_format=mixed
max_binlog_cache_size=16MB
max_binlog_size=1GB
expire_logs_days=30
ft_min_word_len=4
back_log=512
max_allowed_packet=64MB
max_connections=4096
max_connect_errors=100
join_buffer_size=2MB
read_buffer_size=2MB
read_rnd_buffer_size=2MB
sort_buffer_size=2MB
query_buffer_size=64MB
table_open_cache=10000
thread_cache_size=256
max_heap_table_size=64MB
tmp_table_size=64MB
thread_stack=192KB
thread_concurrency=24
local-infile=0
skip-show-database
skip-name-resolve
skip-external-locking
connect_timeout=600
interactive_timeout=600
wait_timeout=600
# *** MyISAM
key_buffer_size=512MB
bulk_insert_buffer_size=64MB
myisam_sort_buffer_size=64MB
myisam_max_sort_file_size=1GB
myisam_repair_threads=1
concurrent_insert=2
myisam_recover
# *** INNODB
innodb_buffer_pool_size=64GB
innodb_additional_mem_pool_size=32MB
innodb_data_file_path=ibdata1:1G;ibdata2:1G:autoextend
innodb_read_io_threads=8
innodb_write_io_threads=8
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=2
innodb_lock_wait_timeout=120
innodb_log_buffer_size=8MB
innodb_log_file_size=256MB
innodb_log_files_in_group=3
innodb_max_dirty_pages_pct=90
innodb_thread_concurrency=16
innodb_open_files=10000
#innodb_force_recovery=4
# *** Replication Slave
read-only
#skip-slave-start
relay-log=relay.log
log-slave-updates
 
MySQL数据库集群实战
如果MySQL优化不能明显改善压力,可以使用高可用、主从复制、读写分离、拆分库、拆分表等方法来进行优化。
MySQL主从复制集群至少需要2台数据库服务器,基中一台为master库,另一台为slave库,MySQL主从数据同步是一个异步复制的过程,要实现复制首先需要在master上开启bin-log日志功能,bin-log日志用于记录在master库中执行的增、删、修改、更新操作的SQL语句,整个过程需要开启3个线程,分别是master开启I/O线程,slave开启I/O线程和SQL线程,具体主从同步原理详解如下:
1、slave上执行slavestart,slave I/O线程会通过在master创建的授权用户连接上至master,并请求master从指定的文件和位置之后发送bin-log日志内容;
2、master接收到来自slaveI/O线程的请求后,master I/O线程根据slave发送的指定bin-log日志position点之后的内容,然后返回给slave的I/O线程;
3、返回的信息中除了bin-log日志内容外,还有masterI/O线程根据slave发送的指定bin-log中的下一个指定更新position点;
4、slaveI/O线程接收到信息后,将接收到的日志内容依次添加到slave端的relay-log文件的最末端,并将读取到的master端的bin-log的文件名和position点记录到master.info文件中,以便在下一次读取的时候能告知master从相应的bin-log文件名及最后一个position点开始发起请求;
5、slaveSQL线程检测到relay-log中内容有更新,会立刻解析relay-log日志中的内容,将解析后的SQL语句在slave里执行,执行成功后slave库与master库数据保持一致。
 
MySQL主从复制实战
MySQL主从复制架构实战步骤:
1、系统环境准备
master        192.168.99.99
slave        192.168.99.100
2、master安装及配置
在/etc/my.cnf配置文件[mysqld]段添加代码:server-id=1        blog-bin=mysql-bin
master端/etc/my.cnf完整配置代码:
[client]
port=3306
socket=/tmp/mysql.sock
[mysqld]
port=3306
socket=/tmp/mysql.sock
skip-external-locking
key_buffer_size=256MB
max_allowed_packet=1MB
table_open_cache=256
sort_buffer_size=1MB
read_buffer_size=1MB
read_rnd_buffer_size=4MB
myisam_sort_buffer_size=64MB
thread_cache_size=8
query_cache_size=16MB
thread_concurrency=8
log-bin=mysql-bin
binlog_format=mixed
server-id=1
[mysqldump]
quick
max_allowed_packet=16MB
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size=128MB
sort_buffer_size=128MB
read_buffer=2MB
write_buffer=2MB
[mysqlhotcopy]
interactive-timeout
在master数据库服务器命令行中创建tang用户及密码并设置权限,执行如下命令,查看bin-log文件及position点:
grant replication slaveon *. * to 'tang'@'%' indentified by '123456';
show masterstatus;    查看bin-log文件名及position点:mysql-bin.000028      257
3、slave安装及配置
slave端安装数据库后,在/etc/my.cnf配置文件[mysqld]段中加入如下代码,然后重启MySQL服务即可。master与slave端server-id不能一样,slave端无须开启bin-log功能:server-id=2
slave端/etc/my.cnf完整配置如下:
[client]
port=3306
socket=/tmp/mysql.sock
[mysqld]
port=3306
socket=/tmp/mysql.sock
skip-external-locking
key_buffer_size=256MB
max_allowed_packet=1MB
table_open_cache=256
sort_buffer_size=1MB
read_buffer_size=1MB
read_rnd_buffer_size=4MB
myisam_sort_buffer_size=64MB
thread_cache_size=8
query_cache_size=16MB
thread_concurrency=8
server-id=2
[mysqldump]
quick
max_allowed_packet=16MB
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size=128MB
sort_buffer_size=128MB
read_buffer=2MB
write_buffer=2MB
[mysqlhotcopy]
interactive-timeout
slave指定masterIP、用户名、密码、bin-log文件名(mysql-bin.000028)及position(257),代码如下:
change master tomaster_host='192.168.99.99',master_user='tang',master_password='123456',master_log_file='mysql-bin.000028',master_log_pos=257;
在slave中启动slavestart,并执行show slave status\G查看MySQL主从状态,代码如下:
slave start;
show slave status\G;
查看slave 端I/O线程、SQL线程状态均为Yes,代表slave已正常连接master实现同步,代码如下:
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
执行Show slavestatus\G常见参数详解:
Slave_IO_Status                I/O线程连接master状态
Master_User                用于连接master的用户
Master_Port                master端监听端口
Connect_Retry                主从连接失败,重试时间间隔
Master_Log_File                I/O线程读取的master二进制日志文件的名称
Read_Master_Log_Pos        I/O线程已读取的master二进制日志文件的位置
Relay_Log_File                SQL线程读取和执行的中继日志文件的名称
Relay_Log_Pos                SQL线程已读取和执行的中继日志文件的位置
Relay_Master_Log_File        SQL线程执行的master二进制日志文件的名称
Slave_IO_Running        I/O线程是否被启动并成功地连接到主服务器上
Slave_SQL_Running        SQL线程是否被启动
Replicate_Do_DB        指定的同步的数据库列表
Skip_Counter                SQL_SLAVE_SKIP_COUNTER设置的值
Seconds_Behind_Master        slave端SQL线程和I/O线程之间的时间差距,单位为秒,常被用于主从延迟检查方法之一。
在master端创建mysql_db_test数据库和t0表,命令如下:
create databasemysql_db_test charset=uft8;
show databases;
use mysql_db_test;
create table t0 (idvarchar(20),name varchar(20));
show tables;
Slave服务器查看是否有mysql_db_test数据库和t0表,如果有则代表slave从master复制数据成功。
 
MySQL主从同步排错思路
MySQL主从同步集群在生产环境使用时,如果主从服务器之间网络通信条件差或者数据库数据量非常大,容易导致MySQL主从同步延迟。
MySQL主从产生延迟后。一旦主库宕机,会导致部分数据没有及时同步至从库,重新启动主库,会导致从库与主库同步错误,快速恢复主从同步关系有如下两种方法:
1、忽略错误后,继续同步。
此方法适用于主从库数据内容相关不大的情况。
master端执行如下命令,将数据库设置为全局读锁,不允许写入新数据。
flush tables with readlock;
slave端停止slaveI/O及SQL线程,同时将同步错误的SQL跳过1次,跳过错误会导致数据不一致,启动start slave,同步状态恢复:
stop slave;
set globalsql_slave_skip_counter=1;
start slave;
2、重新做主从同步,使数据完全同步。
此种方法适用于主从库数据内容相差很大的情况。
master端执行如下命令,将数据库设置全局读锁,不允许写入新数据。
flush tables with readlock;
master端基于mysqldump、xtrabackup工具对数据库进行完整备份,也可以用shell脚本或python脚本实现定时备份,备份成功之后,将完整的数据导入到从库,重新配置主从关系,当slave端的I/O线程、SQL线程均为Yes之后,最后将master端读锁解开即可,解锁命令如下:
unlock tables;