MySQL

最新推荐文章于 2022-01-25 17:02:42 发布

浪浪徐

最新推荐文章于 2022-01-25 17:02:42 发布

阅读量333

点赞数 1

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_33621699/article/details/75151832

版权

关系型数据库管理系统
一个表中可以没有行但是不能没有列，表是由行和列组成的二维关系

第一范式：数据库表的每一列都是不可分割的原子数据项，简而言之，第一范式就是无重复的域
第二范式：需满足第一范式，要求数据库表中的每个实例或记录必须可以被唯一的区分，选取一个能区分每个实体的属性或属性组，作为实体的唯一标识
第三范式：

一个数据集为满足范式需要被拆分成N张表

事务ACID特性

所谓事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。例如，银行转帐工作：从一个帐号扣款并使另一个帐号增款，这两个操作要么都执行，要么都不执行。

　数据库事务必须具备ACID特性，ACID是Atomic（原子性）、Consistency（一致性）、Isolation（隔离性）和Durability（持久性）的英文缩写。

　原子性：指整个数据库事务是不可分割的工作单位。只有使据库中所有的操作执行成功，才算整个事务成功；事务中任何一个SQL语句执行失败，那么已经执行成功的SQL语句也必须撤销，数据库状态应该退回到执行事务前的状态。

　一致性：指数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。例如对银行转帐事务，不管事务成功还是失败，应该保证事务结束后ACCOUNTS表中Tom和Jack的存款总额为2000元。

　隔离性：指的是在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务查看数据更新时，数据所处的状态要么是另一事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看到中间状态的数据。

　持久性：指的是只要事务成功结束，它对数据库所做的更新就必须永久保存下来。即使发生系统崩溃，重新启动数据库系统后，数据库还能恢复到事务成功结束时的状态。

　　事务的（ACID）特性是由关系数据库管理系统（RDBMS，数据库系统）来实现的。数据库管理系统采用日志来保证事务的原子性、一致性和持久性。日志记录了事务对数据库所做的更新，如果某个事务在执行过程中发生错误，就可以根据日志，撤销事务对数据库已做的更新，使数据库退回到执行事务前的初始状态。

　　数据库管理系统采用锁机制来实现事务的隔离性。当多个事务同时更新数据库中相同的数据时，只允许持有锁的事务能更新该数据，其他事务必须等待，直到前一个事务释放了锁，其他事务才有机会更新该数据。

#创建mysql用户和组
    groupadd mysql     //创建mysql组
    useradd -g mysql mysql //创建mysql用户添加到mysql组

CentOS 6
[root@node1 ~]# groupadd -r mysql
[root@bogon ~]# useradd -g mysql mysql
[root@node1 ~]# tar xf mariadb-10.0.10-linux-x86_64.tar.gz -C /usr/local
[root@node1 ~]# cd /usr/local
[root@node1 ~]# ln -sv mariadb-10.0.10-linux-x86_64/ mysql
[root@node1 ~]# cd mysql/
[root@node1 ~]# chown -R root:mysql ./*

[root@node1 ~]# fdisk /dev/sda
[root@node1 ~]# partx -a /dev/sda
[root@node1 ~]# pvcreate /dev/sda6
[root@node1 ~]# vgcreate myvg /dev/sda6
[root@node1 ~]# vgs
[root@node1 ~]# lvcreate -L 10G -n mydata myvg
[root@node1 ~]# yum install -y xfsprogs
[root@node1 ~]# modprobe xfs
[root@node1 ~]# mkfs.xfs /dev/myvg/mydata
[root@node1 ~]# mkdir /mydata
[root@node1 ~]# vim /etc/fstab
            /dev/myvg/mydata        /mydata                 xfs     defaults        0 0
[root@node1 ~]# mount -a
[root@node1 ~]# mount
[root@node1 ~]# cd /mydata
[root@node1 mydata]# mkdir data
[root@node1 mydata]# chown mysql.mysql data
[root@node1 mydata]# cd /usr/local/mysql
[root@node1 mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data/
[root@node1 mysql]# ls
[root@node1 mysql]# cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld
[root@node1 mysql]# chkconfig --add mysqld
[root@node1 mysql]# chkconfig --list mysqld
mysqld          0:off   1:off   2:on    3:on    4:on    5:on    6:off
[root@node1 mysql]# mkdir /etc/mysql
[root@node1 mysql]# cp support-files/my-large.cnf  /etc/mysql/my.cnf
[root@bogon mysql]# vim /etc/mysql/my.cnf
#添加下列三行
                     42 datadir = /mydata/data
                     43 innodb_file_per_table = on
                     44 skip_name_resolve = on  #跳过名称反解
[root@node1 mysql]# service mysqld start
[root@node1 mysql]# ln -s /usr/local/mysql/bin/mysql /usr/bin

Mysql安全初始化

[root@node1 ~]# mysql
Welcome to the MariaDB monitor.  

MariaDB [(none)]> use mysql
Database changed
MariaDB [mysql]> SELECT User,Host,Password FROM user;

MariaDB [mysql]> exit
Bye

#安全初始化
[root@node1 ~]# /usr/local/mysql/bin/mysql_secure_installation

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Set root password? [Y/n] Y  
New password: 
Re-enter new password: 
 ... Success!

Remove anonymous users? [Y/n] Y
 ... Success!

Disallow root login remotely? [Y/n] n  
 ... skipping.

Remove test database and access to it? [Y/n] n
 ... skipping.

Reload privilege tables now? [Y/n] Y
 ... Success!

All done!  

Thanks for using MariaDB!
[root@node1 ~]#

[root@localhost ~]# vi ~/.bash_history

编译安装

#添加mysql用户及用户组
[root@localhost ~]# groupadd -r -g 306 mysql
[root@localhost ~]# useradd -r -g 306 -u 306 mysql
#创建逻辑卷
[root@localhost ~]# fdisk /dev/sda

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): p

Disk /dev/sda: 214.7 GB, 214748364800 bytes
255 heads, 63 sectors/track, 26108 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x000c108b

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1         511     4096000   83  Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2             511        6885    51200000   83  Linux
/dev/sda3            6885       13259    51200000   83  Linux
/dev/sda4           13259       26109   103218176    5  Extended
/dev/sda5           13259       13769     4096000   82  Linux swap / Solaris

Command (m for help): n
First cylinder (13769-26109, default 13769): 
Using default value 13769
Last cylinder, +cylinders or +size{K,M,G} (13769-26109, default 26109): +20G

Command (m for help): t
Partition number (1-6): 6
Hex code (type L to list codes): 8e
Changed system type of partition 6 to 8e (Linux LVM)

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.

WARNING: Re-reading the partition table failed with error 16: Device or resource busy.
The kernel still uses the old table. The new table will be used at
the next reboot or after you run partprobe(8) or kpartx(8)
Syncing disks.
[root@localhost ~]# partx -a /dev/sda
[root@localhost ~]# pvcreate /dev/sda6
  Physical volume "/dev/sda6" successfully created
[root@localhost ~]# vgcreate myvg /dev/sda6
  Volume group "myvg" successfully created
[root@localhost ~]# lvcreate -L 10G -n mydata myvg
[root@localhost ~]# lvs 
[root@localhost ~]# mke2fs -t ext4 /dev/myvg/mydata
[root@localhost ~]# mkdir -pv /mydata
[root@localhost ~]# vim /etc/fstab
 17 /dev/myvg/mydata        /mydata                 ext4    defaults        0 0
[root@localhost ~]# mount
[root@localhost ~]# mount -a
[root@localhost ~]# mount
 [root@localhost ~]# mkdir /mydata/data
mkdir: cannot create directory `/mydata/data': File exists
[root@localhost ~]# chown mysql.mysql /mydata/data 
安装
[root@localhost ~]# yum groupinstall "Development Tools" "Server Platform Development" –y
[root@localhost ~]# yum install cmake -y
[root@localhost ~]# ls
[root@localhost ~]# tar xf mariadb-5.5.46.tar.gz 
[root@localhost ~]# cd  mariadb-5.5.46.tar.gz 
[root@localhost ~]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mariadb-5.5.46 -DMYSQL_DATADIR=/mydata/data -DSYSCONFDIR=/etc -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_ZLIB=system -DWITH_LIBWRAP=0 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci
[root@localhost ~]# make
[root@localhost ~]# make install
[root@localhost ~]# cd /usr/local/mariadb-5.5.46/
[root@localhost mariadb-5.5.46]# ls
[root@localhost mariadb-5.5.46]# chown -R root.mysql ./*
[root@localhost mariadb-5.5.46]# cd ..
[root@localhost local]# ln -sv mariadb-5.5.46/ mysql
[root@localhost local]# cd mysql/
[root@localhost mysql]# ls
[root@localhost mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data
[root@localhost mysql]# ls /mydata/data
[root@localhost mysql]# cp support-files/my-large.cnf /etc/mysql/my.cnf
[root@localhost mysql]# vim /etc/mysql/my.cnf
42 datadir = /mydata/data
43 innodb_file_per_table = ON
44 skip_name_resolve = ON
[root@localhost mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld
[root@localhost mysql]# chmod +x /etc/rc.d/init.d/mysqld
[root@localhost mysql]# chkconfig --add mysqld
[root@localhost mysql]# service mysqld start
#启动
[root@localhost mysql]# /usr/local/mysql/bin/mysql

查看链接线程
MariaDB [hellodb]> SHOW PROCESSLIST;

隔离级别：
未提交读(READ-UNCOMMITTED)  问题：脏读 不可重复读 幻读
提交读(READ-COMMITTED) 问题：不可重复读 幻读
可重读(REPEATABLE-READ)    问题：幻读
可串行化(SERIALIZABLE)  问题：加锁读

脏读：能读到别人尚未提交的数据
不可重复读：两次读到的数据不一致
幻读：可重复读,会产生幻读
加锁读

#查看定义隔离级别   默认为REPEATABLE-READ
MariaDB [hellodb]> SHOW VARIABLES LIKE 'tx_isolation';
修改
MariaDB [hellodb]> SET tx_isolation=’READ_UNCOMMITTED’;

死锁：两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态
事务回滚

MariaDB [hellodb]> START TRANSACTION;
MariaDB [hellodb]> DELETE FROM students WHERE StuID=25;
MariaDB [hellodb]> ROLLBACK;

保存点

MariaDB [hellodb]> START TRANSACTION;
MariaDB [hellodb]> DELETE FROM students WHERE StuID=4;
MariaDB [hellodb]> SAVEPOINT sp1;
MariaDB [hellodb]> ROLLBACK TO sp1;
MariaDB [hellodb]> ROLLBACK;

锁

MariaDB [hellodb]> LOCK TABLES  students READ;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> LOCK TABLES students WRITE;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)


#查看MariaDB 版本号
MariaDB [(none)]> SELECT VERSION();
#查看字符集
MariaDB [(none)]> SHOW CHARACTER SET;
#查看排序规则
MariaDB [(none)]> SHOW COLLATION;
#查看存储引擎
MariaDB [(none)]> SHOW ENGINES;
#创建数据库
MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS testdb;e
MariaDB [testdb]> CREATE SCHEMA IF NOT EXISTS testdb;
#删除数据库
MariaDB [testdb]> DROP DATABASE IF EXISTS testdb; 
MariaDB [testdb]> DROP SCHEMA  IF EXISTS testdb;
#定义默认库
MariaDB [testdb]> USE testdb;
#查看数据表
MariaDB [testdb]> SHOW TABLES;
#查看表结构
MariaDB [hellodb]> DESC students;
#创建数据表
MariaDB [testdb]> CREATE TABLE tb1 (id int NOT NULL,name VARCHAR(30) NOT NULL,age tinyint);

#表中插入数据
MariaDB [hellodb]> INSERT INTO students SET Name='yinjiao',Age='99',Gender='M';
MariaDB [hellodb]> INSERT INTO students (Name,Age,Gender) VALUES ('jinjiao','100','M');
MariaDB [hellodb]> INSERT INTO students VALUES('tom',24,7890.23);

#查询表中数据
MariaDB [hellodb]> SELECT * FROM classes;
MariaDB [hellodb]> SELECT biao1.id,biao1.age,biao2.id,biao2.pname FROM biao1,biao2
MariaDB [hellodb]> SELECT id,name FROM biao;
MariaDB [hellodb]> SELECT * FROM classes WHERE ClassID=4
MariaDB [hellodb]> SHOW TABLE STATUS LIKE 'students';

#WHERE比较运算符
MariaDB [hellodb]> SELECT Name,Age FROM students WHERE Age > 50;
MariaDB [hellodb]> SELECT Name,Age FROM students WHERE Age+30<50;
#WHERE不等于
MariaDB [hellodb]> SELECT Name,Age FROM students WHERE Age != 22;
MariaDB [hellodb]> SELECT Name,Age FROM students WHERE Age <> 22;
#查找列表内数据
MariaDB [hellodb]> SELECT Name,Age FROM students WHERE Age IN (18,100);
#查找某一字段为空
MariaDB [hellodb]> SELECT Name,Age,ClassID FROM students WHERE ClassID IS NULL;

修改
mysql> UPDATE xinbiao SET age = age+5;
mysql> UPDATE xinbiao SET age = age + id;
mysql> UPDATE xinbiao SET age = age + id, sex = 0;
mysql> UPDATE xinbiao SET age = age - 10  WHERE age % 2 = 0;


删除
mysql> DELETE FROM xinbiao id = 5;
mysql> DELETE FROM xinbiao WHERE id< 5;

#排序
MariaDB [hellodb]> SELECT Age AS sAge,StuID AS sid FROM students ORDER BY Age DESC;
#LIMIT 对查询语句作数量限制
MariaDB [hellodb]> SELECT StuID AS sid,Age AS sage FROM students ORDER BY sage DESC LIMIT 10;
#LIMIT 对查询语句作数量限制
MariaDB [hellodb]> SELECT StuID,Age FROM students ORDER BY Age DESC LIMIT 5,3;

#创建索引
MariaDB [hellodb]> ALTER TABLE students ADD INDEX(Age);
MariaDB [hellodb]> CREATE INDEX name ON students (Name);
#查看索引
MariaDB [hellodb]> SHOW INDEXES FROM students;
#查看mysql如何使用索引来处理select语句以及连接表
MariaDB [hellodb]> EXPLAIN SELECT * FROM classes WHERE ClassID=4\G;
#查询缓存命中次数
MariaDB [hellodb]> SHOW GLOBAL STATUS LIKE 'Qcache_hits';
#查询总次数
MariaDB [hellodb]> SHOW GLOBAL STATUS LIKE 'Com_select';
#字段别名
mysql> SELECT id AS uid,age AS uage FROM biao;
#算数
MariaDB [(none)]> SELECT 1+1;
#数据去重
MariaDB [hellodb]> SELECT DISTINCT Gender FROM students;
#等值连接
MariaDB [hellodb]> SELECT * FROM students,teachers WHERE students.TeacherID=teachers.TID;
MariaDB [hellodb]> UPDATE students SET TeacherID=2 WHERE StuID=27;
MariaDB [hellodb]> SELECT s.Name,t.Name FROM students AS s,teachers AS t WHERE s.TeacherID=t.TID;

MariaDB [hellodb]> SELECT s.Name,c.class FROM students AS s,classes AS c WHERE s.ClassID=c.ClassID;
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'query_cache_min_res_unit';


#UNION
MariaDB [hellodb]> SELECT Name FROM students UNION SELECT Name FROM teachers;

#忘记密码
[root@bogon ~]# vim /etc/rc.d/init.d/mysqld
                添加该选项 298  --skip-grant-tables --skip-networking
[root@bogon ~]# service mysqld stop
[root@bogon ~]# service mysqld start
#修改密码
MariaDB [(none)]> UPDATE mysql.user SET password=PASSWORD('456456') WHERE user='root';

[root@bogon ~]# vim /etc/rc.d/init.d/mysqld
                删除该选项 298  --skip-grant-tables --skip-networking
[root@bogon ~]# service mysqld stop
[root@bogon ~]# service mysqld start


[root@bogon ~]# mysqladmin -u root -p ping
[root@bogon ~]# mysqladmin -u root -p create tdb

查看MYSQL默认使用配置
[root@bogon ~]# vim /etc/profile.d/mysql.sh
                export PATH=/usr/local/mysql/bin:$PATH
[root@bogon ~]# . /etc/profile.d/mysql.sh
[root@bogon ~]# mysql --print-defaults
[root@bogon ~]# mysqld --print-defaults

基于sock通信 类似于共享内存  不用经过TCP/IP协议栈 封包和解包的速度更快 
thread_coucurrency 并行线程数 = CPU核心数*2

查询日志  记录查询操作
    general_log=ON|OFF
    general_log_file=HOSTNAME.log
    log_output=TABLE|FILE|NONE

慢查询日志  执行时长超出指定时长的查询操作
    MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE 'long_query_time';
    MariaDB [hellodb]> SELECT @@GLOBAL.long_query_time;
    SET GLOBAL long_query_time
    slow_query_log=ON|OFF 是否启用慢查询日志
    log_slow_queries = ON|OFF
    slow_query_log_file=HOSTNAME-slow.log慢查询日志文件
    log_slow_rate_limit=1 记录速率
    log_slow_verbosity 详细级别 

错误日志
    (1) mysqld启动和关闭过程 输出的信息； 
    (2) mysqld运行中产生的错误信息； 
    (3) event scheduler运行时产生的信息；
    (4) 主从复制架构中，从服务器复制线程启动时产生的日志；

log_error=/PATH
log_warnings={ON|OFF}    是否记录警告日志到错误文件中

二进制日志记录的是导致数据发生改变或潜在导致数据发生改变的SQL语句
    #查看二进制日志文件列表
    MariaDB [hellodb]> SHOW MASTER LOGS;
    MariaDB [hellodb]> SHOW BINARY LOGS;
    #查看正在使用中的二进制日志文件
    MariaDB [hellodb]> SHOW MASTER STATUS;
    MariaDB [hellodb]> SHOW BINLOG EVENTS IN 'mysql-bin.000002';
    sql_log_bin=ON|OFF表示是否记录二进制日志
    binlog_format=STATEMENT|ROW|MIXED二进制日志记录的格式
    max_binlog_size单个二进制日志文件的最大体积   到达最大值会自动滚动 默认为1G
    sync_binlog=1|0是否启用二进制日志同步功能   当事务提交时是否立即将binlog缓冲区中记录下的事件写入到磁盘的二进制日志文件中；1 影响性能 0 影响数据；
mysqlbinlog：客户端命令工具
选项：--start-datetime=  YYYY-MM-DD hh:mm:ss    --stop-datetime= YYYY-MM-DD hh:mm:ss
 --start-position=#    --stop-position=#
查看二进制日志文件内容
[root@localhost ~]# /usr/local/mysql/bin/mysqlbinlog /mydata/data/mysql-bin.000002

二进制日志事件格式：
    # at 553
    #160831  9:56:08 server id 1  end_log_pos 624   Query   thread_id=2     exec_time=0     error_code=0
    SET TIMESTAMP=1472608568/*!*/;
    BEGIN
    /*!*/;

    事件发生的日期时间：#160831  9:56:08
    事件发生的服务器id：server id 1
    事件的结束位置：end_log_pos 624
    事件的类型：Query
    事件发生时所在服务器执行此事件的线程的ID： thread_id=2 
    语句的时间戳与将其写入二进制日志文件中的时间差：exec_time=0
    错误代码：error_code=0
    事件内容：SET TIMESTAMP=1472608568/*!*/;



事务日志：把数据日志中的数据同步到数据文件中   用于崩溃后数据恢复操作。每一次事务提交时，先写事务日志
中继日志：复制架构中，从服务器用于保存主服务器的二进制日志中读取到的事件

mysqldump

[root@localhost ~]# mysqldump -uroot --databases hellodb > /test/beifen.sql
[root@localhost ~]# mysqldump -uroot --all-databases --lock-all-tables --master-data=2  > /test/all.sql

二进制日志还原
[root@localhost lib]# mysqlbinlog --start-position=245 /var/lib/mysql/mysql-bin.000001 > incre.sql

xtrabackup

[root@localhost ~]# yum -y install epel-release
[root@localhost ~]# yum –y install percona-xtrabackup-24-2.4.8-1.el6.x86_64.rpm 
完全备份
[root@localhost ~]# innobackupex --user=root /test/
增量备份
[root@localhost ~]# innobackupex --incremental /test/ --incremental-basedir=/test/1970-01-01_01-01-01
整理完全备份
[root@localhost ~]# innobackupex --apply-log /test/1970-01-01_01-01-01/
合并增量备份
[root@localhost ~]# innobackupex --apply-log --redo-only /test/1970-01-01_01-01-01/ --incremental-dir=/test/1970-01-01_01-01-01/
[root@localhost ~]#systemctl stop mariadb.service
还原
[root@localhost ~]# innobackupex --copy-back /test/1970-01-01_01-01-01/

[root@localhost ~]# cd /mydata/data

[root@localhost data]# chown –R mysql.mysql ./*

[root@localhost ~]#systemctl start mariadb.service

主从复制

    从节点：
        I/O Thread:从Master请求二进制日志事件，并保存于中继日志中；
        SQL Thread:从中继日志中读取日志事件，在本地完成重放;

    主节点：
        dump Thread:为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events;