• Mysql进阶


    Mysql进阶

    mysql源码安装

    //下载源码包
    [root@localhost ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
    [root@localhost ~]# ls
    anaconda-ks.cfg  mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
    
    //创建mysql用户
    [root@localhost ~]# useradd -r -M -s /sbin/nologin mysql
    [root@localhost ~]# id mysql
    uid=994(mysql) gid=991(mysql) groups=991(mysql)
    [root@localhost ~]# grep mysql /etc/group
    mysql:x:991:
    
    //解压到/usr/local下
    [root@localhost ~]# tar xf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
    [root@localhost ~]# ls /usr/local/
    bin  games    lib    libexec                              sbin   src
    etc  include  lib64  mysql-5.7.31-linux-glibc2.12-x86_64  share
    
    //设置软链接
    [root@localhost ~]# ln -s /usr/local/mysql-5.7.31-linux-glibc2.12-x86_64 /usr/local/mysql
    [root@localhost ~]# ll /usr/local/
    total 0
    drwxr-xr-x. 2 root root    6 Aug 12  2018 bin
    drwxr-xr-x. 2 root root    6 Aug 12  2018 etc
    drwxr-xr-x. 2 root root    6 Aug 12  2018 games
    drwxr-xr-x. 2 root root    6 Aug 12  2018 include
    drwxr-xr-x. 2 root root    6 Aug 12  2018 lib
    drwxr-xr-x. 2 root root    6 Aug 12  2018 lib64
    drwxr-xr-x. 2 root root    6 Aug 12  2018 libexec
    lrwxrwxrwx. 1 root root   46 Dec 28 18:35 mysql -> /usr/local/mysql-5.7.31-linux-glibc2.12-x86_64
    drwxr-xr-x. 9 7161 31415 129 Jun  2  2020 mysql-5.7.31-linux-glibc2.12-x86_64
    drwxr-xr-x. 2 root root    6 Aug 12  2018 sbin
    drwxr-xr-x. 5 root root   49 Nov 16 18:43 share
    drwxr-xr-x. 2 root root    6 Aug 12  2018 src
    
    //设置所有者和所有组
    [root@localhost ~]# chown -R mysql.mysql /usr/local/mysql*
    [root@localhost ~]# ll /usr/local/
    total 0
    drwxr-xr-x. 2 root  root    6 Aug 12  2018 bin
    drwxr-xr-x. 2 root  root    6 Aug 12  2018 etc
    drwxr-xr-x. 2 root  root    6 Aug 12  2018 games
    drwxr-xr-x. 2 root  root    6 Aug 12  2018 include
    drwxr-xr-x. 2 root  root    6 Aug 12  2018 lib
    drwxr-xr-x. 2 root  root    6 Aug 12  2018 lib64
    drwxr-xr-x. 2 root  root    6 Aug 12  2018 libexec
    lrwxrwxrwx. 1 mysql mysql  46 Dec 28 18:35 mysql -> /usr/local/mysql-5.7.31-linux-glibc2.12-x86_64
    drwxr-xr-x. 9 mysql mysql 129 Jun  2  2020 mysql-5.7.31-linux-glibc2.12-x86_64
    drwxr-xr-x. 2 root  root    6 Aug 12  2018 sbin
    drwxr-xr-x. 5 root  root   49 Nov 16 18:43 share
    drwxr-xr-x. 2 root  root    6 Aug 12  2018 src
    
    [root@localhost mysql]# ls
    bin  docs  include  lib  LICENSE  man  mysql  README  share  support-files
    
    //设置环境变量
    [root@localhost ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
    [root@localhost ~]# source /etc/profile.d/mysql.sh
    [root@localhost ~]# echo $PATH
    /usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
    [root@localhost ~]# which mysql
    /usr/local/mysql/bin/mysql
    
    //映射include
    [root@localhost mysql]# ln -s /usr/local/mysql/include /usr/include/mysql
    [root@localhost mysql]# ll
    total 288
    drwxr-xr-x.  2 mysql mysql   4096 Jun  2  2020 bin
    drwxr-xr-x.  2 mysql mysql     55 Jun  2  2020 docs
    drwxr-xr-x.  3 mysql mysql   4096 Jun  2  2020 include
    drwxr-xr-x.  5 mysql mysql    205 Jun  2  2020 lib
    -rw-r--r--.  1 mysql mysql 275393 Jun  2  2020 LICENSE
    drwxr-xr-x.  4 mysql mysql     30 Jun  2  2020 man
    lrwxrwxrwx.  1 root  root      19 Dec 28 18:43 mysql -> /usr/include/mysql/
    -rw-r--r--.  1 mysql mysql    587 Jun  2  2020 README
    drwxr-xr-x. 28 mysql mysql   4096 Jun  2  2020 share
    drwxr-xr-x.  2 mysql mysql     90 Jun  2  2020 support-files
    
    //设置帮助文档
    [root@localhost mysql]# vim /etc/man_db.conf 
    MANDATORY_MANPATH                       /usr/local/mysql/man
    
    //设置lib库
    [root@localhost mysql]# vim /etc/ld.so.conf.d/mysql.conf
    /usr/local/mysql/lib
    [root@localhost mysql]# ldconfig
    
    //创建数据存放目录
    [root@localhost ~]# mkdir /opt/data
    [root@localhost ~]# chown -R mysql.mysql /opt/data
    [root@localhost ~]# ll /opt/
    total 0
    drwxr-xr-x. 2 mysql mysql 6 Dec 28 18:51 data
    
    //初始化数据库
    [root@localhost ~]# mysqld --initialize --user=mysql --datadir=/opt/data
    2020-12-28T10:53:56.679439Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
    2020-12-28T10:53:56.912979Z 0 [Warning] InnoDB: New log files created, LSN=45790
    2020-12-28T10:53:56.964328Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
    2020-12-28T10:53:57.029586Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: fbe8d2ec-48fa-11eb-a6f8-000c29f9ec35.
    2020-12-28T10:53:57.030609Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
    2020-12-28T10:53:57.732092Z 0 [Warning] CA certificate ca.pem is self signed.
    2020-12-28T10:53:58.076815Z 1 [Note] A temporary password is generated for root@localhost: ,sWg3*=YF!ik
    
    //保存临时密码
    [root@localhost ~]# echo ',sWg3*=YF!ik' > pass
    [root@localhost ~]# ls
    anaconda-ks.cfg  mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz  pass
    [root@localhost ~]# cat pass 
    ,sWg3*=YF!ik
    
    //写配置文件
    [root@localhost ~]# vim /etc/my.cnf
    [mysqld]
    basedir = /usr/local/mysql
    datadir = /opt/data
    socket = /tmp/mysql.sock
    port = 3306
    pid-file = /opt/data/mysql.pid
    user = mysql
    skip-name-resolve
    
    //配置服务启动脚本
    [root@localhost ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
    [root@localhost ~]# ll /etc/init.d/mysqld 
    -rwxr-xr-x. 1 root root 10576 Dec 28 19:07 /etc/init.d/mysqld
    [root@localhost ~]# vim /etc/init.d/mysqld
    basedir=/usr/local/mysql
    datadir=/opt/data
    
    //启动mysql
    [root@localhost ~]# service mysqld start
    Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'.
     SUCCESS! 
    [root@localhost ~]# ss -antl
    State     Recv-Q     Send-Q         Local Address:Port         Peer Address:Port    
    LISTEN    0          128                  0.0.0.0:22                0.0.0.0:*       
    LISTEN    0          128                     [::]:22                   [::]:*       
    LISTEN    0          80                         *:3306                    *:*  
    
    //修改密码
    [root@localhost ~]# cat pass
    sWg3*=YF!ik
    [root@localhost ~]# mysql -uroot -p',sWg3*=YF!ik'
    mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
    
    //查找是需要装哪个依赖包
    [root@localhost ~]# yum whatprovides libncurses.so.5
    Updating Subscription Management repositories.
    Unable to read consumer identity
    This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
    Last metadata expiration check: 0:18:09 ago on Mon 28 Dec 2020 03:33:21 PM CST.
    ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses compatibility libraries
    Repo        : BaseOS
    Matched from:
    Provide    : libncurses.so.5
    [root@localhost ~]# yum -y install ncurses-compat-libs
    
    
    //用临时密码登录
    [root@localhost ~]# mysql -uroot -p',sWg3*=YF!ik'
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or g.
    Your MySQL connection id is 16
    Server version: 5.7.31
    
    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
    
    //设置新密码
    mysql> set password = password('123456');
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    //设置开机自动启动
    [root@localhost ~]# chkconfig --add mysqld
    [root@localhost ~]# chkconfig mysqld on
    [root@localhost ~]# chkconfig --list
    
    Note: This output shows SysV services only and does not include native
          systemd services. SysV configuration data might be overridden by native
          systemd configuration.
    
          If you want to list systemd services use 'systemctl list-unit-files'.
          To see services enabled on particular target use
          'systemctl list-dependencies [target]'.
    
    mysqld         	0:off	1:off	2:on	3:on	4:on	5:on	6:off
    

    mysql配置文件

    mysql的配置文件为/etc/my.cnf

    [root@localhost ~]# vim .my.cnf
    [client]
    user=root
    password=123456
    
    [root@localhost ~]# mysql -e 'show databases;'
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    [root@localhost ~]# mysql
    Welcome to the MySQL monitor.  Commands end with ; or g.
    Your MySQL connection id is 2
    Server version: 5.7.31 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
    
    mysql> 
    

    mysql常用配置文件参数:

    参数 说明
    port = 3306 设置监听端口
    socket = /tmp/mysql.sock 指定套接字文件位置
    basedir = /usr/local/mysql 指定MySQL的安装路径
    datadir = /data/mysql 指定MySQL的数据存放路径
    pid-file = /data/mysql/mysql.pid 指定进程ID文件存放路径
    user = mysql 指定MySQL以什么用户的身份提供服务
    skip-name-resolve 禁止MySQL对外部连接进行DNS解析 使用这一选项可以消除MySQL进行DNS解析的时间。 若开启该选项,则所有远程主机连接授权都要使用IP地址方 式否则MySQL将无法正常处理连接请求

    mysql破解密码步骤

    1.改配置文件

    [root@localhost ~]# vim /etc/my.cnf
    skip-grant-tables
    

    2.重启服务

    [root@localhost ~]# service mysqld restart
    Shutting down MySQL.. SUCCESS! 
    Starting MySQL. SUCCESS! 
    

    3.登录数据库并修改密码

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    4 rows in set (0.01 sec)
    
    mysql> use mysql;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    mysql> show tables;
    +---------------------------+
    | Tables_in_mysql           |
    +---------------------------+
    | columns_priv              |
    | db                        |
    | engine_cost               |
    | event                     |
    | func                      |
    | general_log               |
    | gtid_executed             |
    | help_category             |
    | help_keyword              |
    | help_relation             |
    | help_topic                |
    | innodb_index_stats        |
    | innodb_table_stats        |
    | ndb_binlog_index          |
    | plugin                    |
    | proc                      |
    | procs_priv                |
    | proxies_priv              |
    | server_cost               |
    | servers                   |
    | slave_master_info         |
    | slave_relay_log_info      |
    | slave_worker_info         |
    | slow_log                  |
    | tables_priv               |
    | time_zone                 |
    | time_zone_leap_second     |
    | time_zone_name            |
    | time_zone_transition      |
    | time_zone_transition_type |
    | user                      |
    +---------------------------+
    31 rows in set (0.00 sec)
    
    mysql> select * from userG;
    *************************** 1. row ***************************
                      Host: localhost
                      User: root
               Select_priv: Y
               Insert_priv: Y
               Update_priv: Y
               Delete_priv: Y
               Create_priv: Y
                 Drop_priv: Y
               Reload_priv: Y
             Shutdown_priv: Y
              Process_priv: Y
                 File_priv: Y
                Grant_priv: Y
           References_priv: Y
                Index_priv: Y
                Alter_priv: Y
              Show_db_priv: Y
                Super_priv: Y
     Create_tmp_table_priv: Y
          Lock_tables_priv: Y
              Execute_priv: Y
           Repl_slave_priv: Y
          Repl_client_priv: Y
          Create_view_priv: Y
            Show_view_priv: Y
       Create_routine_priv: Y
        Alter_routine_priv: Y
          Create_user_priv: Y
                Event_priv: Y
              Trigger_priv: Y
    Create_tablespace_priv: Y
                  ssl_type: 
                ssl_cipher: 
               x509_issuer: 
              x509_subject: 
             max_questions: 0
               max_updates: 0
           max_connections: 0
      max_user_connections: 0
                    plugin: mysql_native_password
     authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
          password_expired: N
     password_last_changed: 2020-12-28 19:26:13
         password_lifetime: NULL
            account_locked: N
    *************************** 2. row ***************************
                      Host: localhost
                      User: mysql.session
               Select_priv: N
               Insert_priv: N
               Update_priv: N
               Delete_priv: N
               Create_priv: N
                 Drop_priv: N
               Reload_priv: N
             Shutdown_priv: N
              Process_priv: N
                 File_priv: N
                Grant_priv: N
           References_priv: N
                Index_priv: N
                Alter_priv: N
              Show_db_priv: N
                Super_priv: Y
     Create_tmp_table_priv: N
          Lock_tables_priv: N
              Execute_priv: N
           Repl_slave_priv: N
          Repl_client_priv: N
          Create_view_priv: N
            Show_view_priv: N
       Create_routine_priv: N
        Alter_routine_priv: N
          Create_user_priv: N
                Event_priv: N
              Trigger_priv: N
    Create_tablespace_priv: N
                  ssl_type: 
                ssl_cipher: 
               x509_issuer: 
              x509_subject: 
             max_questions: 0
               max_updates: 0
           max_connections: 0
      max_user_connections: 0
                    plugin: mysql_native_password
     authentication_string: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
          password_expired: N
     password_last_changed: 2020-12-28 18:53:58
         password_lifetime: NULL
            account_locked: Y
    *************************** 3. row ***************************
                      Host: localhost
                      User: mysql.sys
               Select_priv: N
               Insert_priv: N
               Update_priv: N
               Delete_priv: N
               Create_priv: N
                 Drop_priv: N
               Reload_priv: N
             Shutdown_priv: N
              Process_priv: N
                 File_priv: N
                Grant_priv: N
           References_priv: N
                Index_priv: N
                Alter_priv: N
              Show_db_priv: N
                Super_priv: N
     Create_tmp_table_priv: N
          Lock_tables_priv: N
              Execute_priv: N
           Repl_slave_priv: N
          Repl_client_priv: N
          Create_view_priv: N
            Show_view_priv: N
       Create_routine_priv: N
        Alter_routine_priv: N
          Create_user_priv: N
                Event_priv: N
              Trigger_priv: N
    Create_tablespace_priv: N
                  ssl_type: 
                ssl_cipher: 
               x509_issuer: 
              x509_subject: 
             max_questions: 0
               max_updates: 0
           max_connections: 0
      max_user_connections: 0
                    plugin: mysql_native_password
     authentication_string: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
          password_expired: N
     password_last_changed: 2020-12-28 18:53:58
         password_lifetime: NULL
            account_locked: Y
    3 rows in set (0.00 sec)
    
    mysql> update user set authentication_string=password('654321') where User='root' and Host='localhost';
    Query OK, 1 row affected, 1 warning (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 1
    
    mysql> quit
    Bye
    

    4.修改配置文件,删除skip-grant-tables

    [root@localhost ~]# vim /etc/my.cnf
    删除skip-grant-tables
    

    5.重启服务

    [root@localhost ~]# service mysqld restart
    Shutting down MySQL.. SUCCESS! 
    Starting MySQL. SUCCESS!
    

    6.验证密码是否修改成功

    [root@localhost ~]# mysql -uroot -p123456
    mysql: [Warning] Using a password on the command line interface can be insecure.
    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
    [root@localhost ~]# mysql -uroot -p654321
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or g.
    Your MySQL connection id is 3
    Server version: 5.7.31 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
    
    mysql>
    

    mysql数据库备份与恢复

    数据库常用备份方案

    数据库备份方案:

    • 全量备份
    • 增量备份
    • 差异备份
    备份方案 特点
    全量备份 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。 数据恢复快。 备份时间长
    增量备份 增量备份是指在一次全备份或上一次增量备份后,以后每次的备份只需备份 与前一次相比增加和者被修改的文件。这就意味着,第一次增量备份的对象 是进行全备后所产生的增加和修改的文件;第二次增量备份的对象是进行第一次增量 备份后所产生的增加和修改的文件,如此类推。 没有重复的备份数据 备份时间短 恢复数据时必须按一定的顺序进行
    差异备份 备份上一次的完全备份后发生变化的所有文件。 差异备份是指在一次全备份后到进行差异备份的这段时间内 对那些增加或者修改文件的备份。在进行恢复时,我们只需对第一次全量备份和最后一次差异备份进行恢复。

    mysql备份工具mysqldump

    //准备工作
    mysql> create database school;
    Query OK, 1 row affected (0.01 sec)
    
    mysql> use school;
    Database changed
    mysql> create table student(id int not null primary key auto_increment,name varchar(50),age tinyint);
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> insert student(name,age) values('tom',20),('jerry',15),('tom',23);
    Query OK, 3 rows affected (0.01 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    mysql> select * from student;
    +----+-------+------+
    | id | name  | age  |
    +----+-------+------+
    |  1 | tom   |   20 |
    |  2 | jerry |   15 |
    |  3 | tom   |   23 |
    +----+-------+------+
    3 rows in set (0.00 sec)
    mysql> show tables;
    +------------------+
    | Tables_in_school |
    +------------------+
    | student          |
    +------------------+
    1 row in set (0.00 sec)
    
    mysql> quit
    Bye
    
    //备份所有数据库
    [root@localhost ~]# mysqldump -uroot -p654321 --all-databases > all.sql
    mysqldump: [Warning] Using a password on the command line interface can be insecure.
    [root@localhost ~]# ls
    all.sql  anaconda-ks.cfg  mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz  pass
    [root@localhost ~]# file all.sql 
    all.sql: UTF-8 Unicode text, with very long lines
    
    //不小心删除数据库
    [root@localhost ~]# mysql -uroot -p654321 -e 'drop database school;'
    mysql: [Warning] Using a password on the command line interface can be insecure.
    [root@localhost ~]# mysql -uroot -p654321 -e 'show databases;'
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    
    //恢复数据库
    [root@localhost ~]# mysql -uroot -p654321 < all.sql
    mysql: [Warning] Using a password on the command line interface can be insecure.
    [root@localhost ~]# mysql -uroot -p654321 -e 'show databases;'
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | school             |
    | sys                |
    +--------------------+
    [root@localhost ~]# mysql -uroot -p654321 -e 'select * from school.student;'
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +----+-------+------+
    | id | name  | age  |
    +----+-------+------+
    |  1 | tom   |   20 |
    |  2 | jerry |   15 |
    |  3 | tom   |   23 |
    +----+-------+------+
    
  • 相关阅读:
    深度学习:Keras入门(一)之基础篇(转)
    《神经网络与机器学习》导言
    GAN综述
    VS2017专业版和企业版激活密钥
    IntelliJ Idea 常用快捷键列表
    数据库SQL优化大总结之 百万级数据库优化方案
    git使用教程
    VS2015常用快捷键总结
    心跳包实现
    基于OAuth 2.0的第三方认证 -戈多编程
  • 原文地址:https://www.cnblogs.com/yuqinghao/p/14203335.html
Copyright © 2020-2023  润新知