mysql

作者: Icarus_ac47 | 来源:发表于2018-09-20 19:58 被阅读0次

    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
    
    1. 初始化
    [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

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

    1. 修改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
    
    1. 安装

    安装节点,四台机器,需要重复四次:(管理节点不用安装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目录。.

    1. 配置

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

    # 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
    
    1. 按顺序启动各个节点:

    启动管理节点:

    [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-M-S-S 准备Mycat连接的用户及权限 
    192.168.122.234 为Mycat主机IP


    2222.PNG

    相关文章

      网友评论

          本文标题:mysql

          本文链接:https://www.haomeiwen.com/subject/pmrnnftx.html