CentOS7.x 安装mysql5.7 XtraBackUp备份工具使用命令详解_Mysql_

 # 官网 https://www.mysql.com/ # 下载模块 https://downloads.mysql.com/archives/community/ # 官网下载链接 https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.18-1.el7.x86_64.rpm-bundle.tar

mkdir /app && mkdir /app/mysql57 && cd /app/mysql57 # lunix下载命令 wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.18-1.el7.x86_64.rpm-bundle.tar # 解压 tar -xvf mysql-5.7.18-1.el7.x86_64.rpm-bundle.tar [root@localhost mysql57]# ls mysql-5.7.18-1.el7.x86_64.rpm-bundle.tar mysql-community-libs-5.7.18-1.el7.x86_64.rpm mysql-community-client-5.7.18-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.18-1.el7.x86_64.rpm mysql-community-common-5.7.18-1.el7.x86_64.rpm mysql-community-minimal-debuginfo-5.7.18-1.el7.x86_64.rpm mysql-community-devel-5.7.18-1.el7.x86_64.rpm mysql-community-server-5.7.18-1.el7.x86_64.rpm mysql-community-embedded-5.7.18-1.el7.x86_64.rpm mysql-community-server-minimal-5.7.18-1.el7.x86_64.rpm mysql-community-embedded-compat-5.7.18-1.el7.x86_64.rpm mysql-community-test-5.7.18-1.el7.x86_64.rpm mysql-community-embedded-devel-5.7.18-1.el7.x86_64.rpm [root@localhost mysql57]# 

# 安装 community-common rpm -ivh mysql-community-common-5.7.18-1.el7.x86_64.rpm ​ # 卸载 mariadb rpm -qa | grep mariadb ​ [root@localhost mysql57]# rpm -qa | grep mariadb mariadb-libs-5.5.68-1.el7.x86_64 ​ # 卸载 rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64 # 安装库 rpm -ivh --force --nodeps mysql-community-libs-compat-5.7.18-1.el7.x86_64.rpm # 安装客户端 rpm -ivh --force --nodeps mysql-community-client-5.7.18-1.el7.x86_64.rpm # 安装net-tools yum install net-tools -y # 安装server rpm -ivh --force --nodeps mysql-community-server-5.7.18-1.el7.x86_64.rpm # 检查安装情况 mysql -uroot -p # 查看mysql 安装目录 which mysql /usr/bin/mysql

vim /etc/my.cnf #在 [mysqld]下面 添加 跳过登录校验 # 跳过登录校验 skip-grant-tables # 修改mysql服务端口 也可以不换 port=23306 # 启动mysql systemctl start mysqld.service # 进入mysql mysql # 设置登录密码 update mysql.user set authentication_string=password('admin123') where user='root'; # 刷新 flush privileges; # 推出 exit; ​ # 重启 systemctl restart mysqld.service # 停止 systemctl stop mysqld.service # 注释掉登录校验 vim /etc/my.cnf #在 [mysqld]下面 添加 跳过登录校验 # 跳过登录校验 # skip-grant-tables ​ # 启动 systemctl start mysqld.service ​ # 登录 mysql -h 127.0.0.1 -P 3306 -u root -padmin123 mysql -uroot -padmin123 ​ # 设置密码的验证强度等级，设置 validate_password_policy 的全局参数为 LOW set global validate_password_policy=LOW; //设置最小长度 set global validate_password_length=4; ​ set password=password('admin123'); ​ #在 mysql 数据库的 user 表中查看当前 root 用户的相关信息 select host, user, authentication_string, plugin from user; #授权 root 用户的所有权限并设置远程访问 ​ ​ #刷新权限列表 flush privileges; # 增加新用户 格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码" # 如，增加一个用户rent密码为admin123，让其可以在本机上登录， 并对所有数据库有查询、插入、修改、删除的权限。 grant select,insert,update,delete on *.* to rent@localhost Identified by "admin123" with grant option; ​ grant all privileges on *.* to 'root'@'%' Identified by "admin123" with grant option; ​ flush privileges; exit; # 打开mysql 防火墙 33306 /没修改端口则是3306 firewall-cmd --zone=public --add-port=3306/tcp --permanent # 重新载入 firewall-cmd --reload #opyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved. # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; version 2 of the License. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software # Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA ​ # # The MySQL Server configuration file. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html ​ [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /app/mysql57/mysql #log-error = /var/log/mysql/error.log # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 ​ event_scheduler=ON max_connections = 2000 max_user_connections = 1900 max_connect_errors = 100000 max_allowed_packet = 50M lower_case_table_names=1 character_set_server=utf8 collation-server=utf8_general_ci log_timestamps=SYSTEM default-time-zone = '+8:00' [mysqld] skip-name-resolve sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION wait_timeout=315360 interactive_timeout=31536000 #开启慢日志 slow_query_log = ON slow_query_log_file=/var/log/mysql/error.log long_query_time=4

备份方式的二分类维度：状态、格式内容 备份方式并没有绝对的好坏，只有不同的用途 1.备份时数据库的状态 2.备份文件的格式 3.备份的内容

Hot Backup (热备)：正常运行中直接备份 Cold BackUp (冷备)：完全停止后备份 Warm BackUp （冷备）：数据库只读

1.逻辑备份：输出文件或SQL语句 2.物理备份（裸文件）：备份数据库底层文件

1.完全备份: 备份完整数据 2.增量备份：备份上次备份的数据差异 3.日志备份：备份Binlog

mysqldump：逻辑、热、全量备份 xtrabackup: 物理、热、全量+增量备份

1.Mysql原生的SQL指令 2.最原始的逻辑备份方式 3.备份的功能和效果取决于如何写SQL语句 4.在innoDB事务下。可以做到一致性试图 5.修改分隔符：fileds terminated by 6.修改换行符： lines terminated by ​ 缺点： 1.输出的文本比较简略 2.很难进行还原，现在往往用来简单的导出

# mysql 可以操作的文件夹 show variables like '%secure%'; +--------------------------+-----------------------+ | Variable_name | Value | +--------------------------+-----------------------+ | require_secure_transport | OFF | | secure_auth | ON | | secure_file_priv | /var/lib/mysql-files/ | +--------------------------+-----------------------+ # 使用into outfile 指令将查询结果到处至文件 select * into outfile '/var/lib/mysql-files/out_file_test' from Z; # 查询数据库 show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ # 切换库 use sys; # 查询表 show tables; +-----------------------------------------------+ | Tables_in_sys | +-----------------------------------------------+ | host_summary | | host_summary_by_file_io | | host_summary_by_file_io_type | ​ # 查询语句 select * from host_summary; +-------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+ | host | statements | statement_latency | statement_avg_latency | table_scans | file_ios | file_io_latency | current_connections | total_connections | unique_users | current_memory | total_memory_allocated | +-------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+ | 192.168.1.8 | 50 | 11.16 ms | 223.24 us | 24 | 0 | 0 ps | 1 | 7 | 2 | 0 bytes | 0 bytes | | localhost | 175 | 36.15 ms | 206.55 us | 9 | 174 | 13.49 ms | 1 | 2 | 1 | 0 bytes | 0 bytes | +-------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+ ​ # 将查询的结果放到文件里面 select * into outfile
                
提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                
                

                

                                            
上一篇：MySQL基础入门教程之事务_Mysql_
                                                                
下一篇：教你使用MySQL Shell连接数据库的方法_Mysql_