CentOS 6.5安装XtraBackup 还原RDS Mysql备份文件到本地Mysql服务器

有个网站的数据库用了下RDS,本文记录将RDS备份文件backup.gz还原到本地Mysql服务器中的流程。

顺便解决了innobackupex_55: command not found报错的问题。

MySQL

安装XtraBackup工具,去官网根据对应系统版本,安装XtraBackup,我安装的是比较经典稳定的2.1.5版

wget https://www.percona.com/downloads/XtraBackup/XtraBackup-2.1.5/RPM/rhel6/x86_64/percona-xtrabackup-2.1.5-680.rhel6.x86_64.rpm

安装依赖包

yum install -y perl-DBD-MySQL perl-DBI  perl-Time-HiRes libaio*

安装

rpm -ivh percona-xtrabackup-2.1.5-680.rhel6.x86_64.rpm

查看安装成功没有

rpm -qa |grep  xtrabackup

显示

percona-xtrabackup-2.1.5-680.rhel6.x86_64

rpm -ql percona-xtrabackup-2.1.5-680.rhel6.x86_64

显示

/usr/bin/innobackupex
/usr/bin/innobackupex-1.5.1
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/bin/xtrabackup_55
/usr/bin/xtrabackup_56
/usr/share/doc/percona-xtrabackup-2.1.5
/usr/share/doc/percona-xtrabackup-2.1.5/COPYING

使用wget从下载备份文件,下载下来的文件需要重新命名 使用mv命令重命名为backu.gz

下载xtrabackup程序完成后,首先解压backup.gz,再使用xtrabackup中附带的解包工具进行解包。
执行命令:

mkdir -p /home/mysql/data_dir && gzip -d -c backup.gz | xbstream -x -C /home/mysql/data_dir

完成解压及解包后应用innodb的redo-log,执行命令(注意百度教程中是错的)

以下是百度教程中的错误命令:

innobackupex_55 --defaults-file=/home/mysql/data_dir/backup-my.cnf --apply-log /home/mysql/data_dir

但是,innobackupex_55命令执行后报错,如下:

# innobackupex_55 --defaults-file=/home/mysql/data_dir/backup-my.cnf --apply-log /home/mysql/data_dir
-bash: innobackupex_55: command not found

发百度工单瞎耽误工夫,最后自己搞定了,正确的命令为:

innobackupex --defaults-file=/home/mysql/data_dir/backup-my.cnf --apply-log /home/mysql/data_dir

执行后出现以下信息则说明成功了

命令执行完毕后,参考/home/mysql/data_dir/backup-my.cnf文件修改mysql数据库配置中的innodb_log_file_size参数;
使用/home/mysql/data_dir文件夹作为数据目录启动mysql即可。

vi /etc/my.cnf

将原来的datadir = /usr/local/mysql/var注释掉(怎么注释?语句前面加一个#号)
然后将以下的配置加入my.cnf文件中,注意加在原my.cnf文件中的[mysqld]下面,:wq命令保存

[mysqld]
innodb_checksum_algorithm=innodb
innodb_log_checksum_algorithm=innodb
innodb_data_file_path=ibdata1:32M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=268435456
innodb_fast_checksum=0
innodb_page_size=16384
innodb_log_block_size=512
innodb_undo_tablespaces=0

然后重启Mysql服务

/etc/init.d/mysql restart

Linux CentOS 7.2 MySQL的root密码忘记时重置方法

验证环境:

[root@~~/]# rpm -qa | grep mysql
mysql-5.6.28-1.el6.x86_64
[root@~~/]# lsb_release -a
LSB Version:    :core-4.1-amd64:core-4.1-noarch
Distributor ID: CentOS
Description:    CentOS Linux release 7.2.1511 (Core)
Release:        7.2.1511
Codename:       Core
[root@~~/]# uname -r
3.10.0-327.22.2.el7.x86_64

首先确认服务器出于安全的状态,也就是没有人能够任意地连接MySQL数据库。
因为在重新设置mysql的root密码的期间,MySQL数据库完全出于没有密码保护的状态下,其他的用户也可以任意地登录和修改MySQL的信息。可以采用将MySQL对外的端口封闭,并且停止Apache以及所有的用户进程的方法实现服务器的准安全状态。最安全的状态是到服务器的Console上面操作,并且拔掉网线。

修改MySQL的登录设置:

在[mysqld]的段中加上一句:skip-grant-tables
[root@~~/]# vi /etc/my.cnf
例如: 
[mysqld] 
datadir=/var/lib/mysql 
socket=/var/lib/mysql/mysql.sock 
skip-grant-tables 
保存并且退出vi。

重新启动mysqld

[root@~~/]# /etc/init.d/mysqld restart 
Stopping MySQL: [ OK ] 
Starting MySQL: [ OK ]

登录并修改MySQL的root密码

[root@~~/]# mysql
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.6.28-log Source distribution
Copyright (c) 2000, 2015, 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> use mysql;
mysql> update user set password=password("test") where user='root';
mysql> flush privileges;
mysql> exit; 
Bye

将MySQL的登录设置修改回来

将刚才在[mysqld]的段中加上的skip-grant-tables删除,保存并且退出vi;

[root@~~/]# vi /etc/my.cnf
保存并且退出vi。

再次重新启动mysqld

[root@~~/]# /etc/init.d/mysqld restart 
Stopping MySQL: [ OK ] 
Starting MySQL: [ OK ]

使用新的密码登录,正常登录

如果外网不能访问数据库,可以进行如下操作:

[root@~~/]# firewall-cmd --permanent --zone=public --add-port=3306/tcp  
success
[root@~~/]# firewall-cmd --reload
success

设置MySQL数据库表名不区分大小写的方法

问题说明

MySQL 如何设置不区分表名大小写。

处理办法

一般情况下 Linux 服务器默认安装 MySQL 的数据库表名是区分大小写的,如果 ECS 上安装的 MySQL 不支持表名区分大小下,则按照如下方法操作即可:

  1. 用 root 登录,修改 /etc/my.cnf (注意:以实际 my.cnf 配置文件路径为准)

  2. 在 [mysqld] 节点下,加入一行: lower_case_table_names=1

  3. 重启 MySQL 即可;

centos7配置MariaDB允许指定IP远程连接数据库

前提

公司数据库没有版本控制,这个就相当坑爹了,问以前的技术,就是在本地建一个表,然后导入到线上,WTF。目前就只能临时开启远程连接数据了,后面在使用版本控制

防火墙

centos7 之前的防火墙是不一样的,比如你要添加3306端口:

 ## 全部
 iptables -A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT

 ## 部分ipiptables
 iptables -A INPUT -p tcp -s 138.111.21.11 -dport 3306 -j ACCEP

 service iptables save

 service iptables restart

 ## 查看 iptables
 iptables -L -n

但这个在centos7 就不好使,查看文档才知道centos7 使用了增强版firewall

 firewall-cmd --zone=public --permanent --add-port=3306/tcp
  1、firwall-cmd:是Linux提供的操作firewall的一个工具;
  2、--permanent:表示设置为持久;
  3、--add-port:标识添加的端口;
  4、--zone=public:指定的zone为public;

当然如果不太习惯使用命令,我们可以直接改配置文件

MySQL

进入etc/firewalld/zone中,修改public.xml

<?xml version="1.0" encoding="utf-8"?>
<zone>
  <short>Public</short>
  <description>For use in public areas.</description>
  <rule family="ipv4">
    <source address="122.10.70.234"/>
    <port protocol="udp" port="514"/>
    <accept/>
  </rule>
  <rule family="ipv4">
    <source address="123.60.255.14"/>
    <port protocol="tcp" port="10050-10051"/>
    <accept/>
  </rule>
 <rule family="ipv4">
    <source address="192.249.87.114"/> 放通指定ip,指定端口、协议
    <port protocol="tcp" port="80"/>
    <accept/>
  </rule>
<rule family="ipv4"> 放通任意ip访问服务器的9527端口
    <port protocol="tcp" port="9527"/>
    <accept/>
  </rule>
</zone>

上述配置文件可以看出:

1、添加需要的规则,开放通源ip为122.10.70.234,端口514,协议tcp;
2、开放通源ip为123.60.255.14,端口10050-10051,协议tcp;/3、开放通源ip为任意,端口9527,协议

firewall 常用命令

# 重启
service firewalld restart 

# 开启
service firewalld start 

# 关闭
service firewalld stop

# 查看firewall 服务状态
 systemctl status firewall

# 查看防火墙
 firewall-cmd  --list-all    

MySQL

开启服务器3306对外开放后,还需要设置数据库用户授权

MariaDB 开启远程连接

在数据库mysql 中的user表中可以看到默认是只能本地连接的,所有可以添加一个用户

# 针对ip
create user 'root'@'192.168.10.10' identified by 'password';

#全部
 create user 'root'@'%' identified by 'password';

建议还是针对于ip开放吧,不要全部开放

授权用户:

 # 给用户最大权限
  grant all privileges on *.* to 'root'@'%' identified by 'password';

 # 给部分权限(test 数据库)

  grant all privileges on test.* to 'root'@'%' identified by 'password' with grant option;

# 刷新权限表

 flush privileges;

# show grants for 'root'@'localhost';

MySQL

接下来就是可以本地连接了

参考文章:

  • 防火墙:
    http://blog.csdn.net/xlgen157387/article/details/52672988

    http://blog.sina.com.cn/s/blog_4c197d4201017rgl.html

  • 用户授权:
    http://www.cnblogs.com/xujishou/p/6306765.html

CentOS安装配置MariaDB Galera Cluster集群

MariaDB Galera Cluster是MariaDB的同步多主集群。它仅在Linux上可用,并且仅支持xtraDB/InnoDB存储引擎,对MyISAM也有一定的支持(https://mariadb.com/kb/en/mariadb/galera-cluster-system-variables/#wsrep_replicate_myisam),

特征:

  • 同步复制
  • 多主可同时读写 Active-active multi-master
  • 任何节点成员可读写
  • 自动成员控制,故障节点从群集中删除
  • 节点可自动加入(基于配置)
  • 真正的并行复制,在行级复制
  • 使用者在客户端连接,在使用和感官上和mysql一样

优点:

  • 没有从库延迟
  • 不会丢失事物
  • 读写扩展高(后续文章会加入读写分离)
  • 多主,不存在slave 延迟,也不以来binlog
  • 具有同步复制,故障切换和重新同步的高可用性解决方案
  • 所有服务器都具有最新数据(无滞后)
  • 跨数据中心的高可用性

它依赖于wsrep API(https://launchpad.net/wsrep):

wsrep API定义了一组应用程序回调和复制库调用,以实现事务数据库和类似应用程序的同步写入复制。在从应用程序详细信息中抽象和分离复制实现。虽然此接口的主要目标是基于认证的多主机复制,但同样适用于异步和同步主/从复制。

它的复制过程也是基于认证的,基于WSREP API,大量的配置参考:

https://mariadb.com/kb/en/mariadb/galera-cluster-system-variables/#wsrep_replicate_myisam

Galera可以利用四个端口:

  • 3306 数据库端口
  • 4567 对于Galera Cluster复制,组播复制在此端口上同时使用UDP传输和TCP。
  • 4568 增量数据同步IST,节点下线、重启后使用该端口,增量同步数据,增量状态转移。
  • 4444 镜像数据传输SST,集群数据同步端口,全量同步,新节点加入时起作用

三台机器:10.0.1.49,10.10.240.113,10.0.1.61
其实用起来可以是这样的:

MySQL

前面使用LVS等进行调度,当然在中间可以使用中间件进行读写分离

I. yum安装选择

https://downloads.mariadb.org/mariadb/repositories/
centos7 yum如下:

[root@LinuxEA ~]# cat > /etc/yum.repos.d/mariadb.repo << EOF
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF
[root@LinuxEA ~]# yum install socat MariaDB-Galera-server MariaDB-client rsync galera

其他版本tar包安装下载地址:
https://downloads.mariadb.org/mariadb-galera/+releases/

本次使用二进制安装:
mariadb-galera安装:http://download.nus.edu.sg/mirror/mariadb//mariadb-galera-10.0.30/bintar-linux-x86_64/mariadb-galera-10.0.30-linux-x86_64.tar.gz

galera安装:http://releases.galeracluster.com/centos/7/x86_64/galera-3-25.3.20-2.el7.x86_64.rpm

防火墙添加:

iptables -I INPUT 4 -p tcp -m tcp -m state --state NEW -m multiport --dports 3306,4444,4567,4568 -m comment --comment &quot;mariadb-galera&quot; -j ACCEPT

其他依赖包:

yum install socat MariaDB-client rsync galera lsof

文档参考

  • 编译参考:https://mariadb.com/kb/en/mariadb/installating-galera-from-source/
  • 理解参考:https://www.percona.com/blog/2014/09/01/galera-replication-how-to-recover-a-pxc-cluster/
  • 其他教程:https://severalnines.com/resources/tutorials/galera-cluster-mysql-tutorial
  • http://galeracluster.com/products/technology/
  • Galera Cluster 局限:https://mariadb.com/kb/en/mariadb/mariadb-galera-cluster-known-limitations/

II. node1

解压

[root@LinuxEA /data]# tar xf mariadb-galera-10.0.30-linux-x86_64.tar.gz -C /usr/local/
[root@LinuxEA /data]# cd /usr/local/
[root@LinuxEA /usr/local]# ln -s mariadb-galera-10.0.30-linux-x86_64/ mysql

安装

[root@LinuxEA /usr/local]# useradd mysql -s /sbin/nologin -M
[root@LinuxEA /usr/local]# mkdir -p /data/mysql 
[root@LinuxEA /usr/local]# chown -R mysql.mysql  /data/mysql 
[root@LinuxEA /usr/local]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/data/mysql/ --basedir=/usr/local/mysql

复制启动脚本

[root@LinuxEA /usr/local]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@LinuxEA /usr/local]# systemctl enable mysqld
mysqld.service is not a native service, redirecting to /sbin/chkconfig.
Executing /sbin/chkconfig mysqld on
[root@LinuxEA /usr/local]# 

安装jemalloc内存分配器

[root@LinuxEA /]# wget https://github.com/jemalloc/jemalloc/releases/download/4.2.1/jemalloc-4.2.1.tar.bz2
[root@LinuxEA /]# yum install -y gcc lsof
[root@LinuxEA /]# tar xf jemalloc-4.2.1.tar.bz2
[root@LinuxEA /]# cd jemalloc-4.2.1/
[root@LinuxEA /jemalloc-4.2.1]# ./configure
[root@LinuxEA /jemalloc-4.2.1]# make 
[root@LinuxEA /jemalloc-4.2.1]# make install

授权用户:

grant all privileges on *.* to 'tb'@'%' identified by 'password';
flush privileges;

III. 配置文件部分说明:

  1. wsrep_cluster_address=gcomm:// 如下:
    这条命令,gcomm://是一个特殊的参数,在启动第一台数据库时需要使用这个参数来启动,否则会启动失败

  2. wsrep_cluster_address=”gcomm://10.0.1.49,10.10.240.113,10.0.1.61″如下:
    gcomm://后的是集群成员的ip地址

  3. wsrep_sst_auth=tb:password如下:这里的sst需要安装lsof依赖包
    这个参数就是我们之前设定的用来同步的用户名和密码

  4. wsrep_node_name=’node1′ 如下:
    当前node

  5. wsrep_node_address=’10.0.1.49′ 如下:
    当前node ip地址

IV. 部分配置文件如下

galera

[galera]
##https://www.percona.com/doc/percona-xtradb-cluster/5.6/wsrep-system-index.html
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
#wsrep_cluster_address=&quot;gcomm://&quot;
wsrep_cluster_address=&quot;gcomm://10.0.1.49,10.10.240.113,10.0.1.61&quot;
wsrep_cluster_name='cluster'
wsrep_node_address='10.0.1.49'
wsrep_node_name='node1'
#wsrep_replicate_myisam=1
####wsrep_slave_threads = 8
#wsrep_slave_threads = 16
##wsrep_provider_options=&quot;gcs.fc_limit=512&quot;
#wsrep_provider_options=&quot;gcache.page_size=128M;gcache.size=2G;gcs.fc_limit=512;gcs.fc_factor=0.9;evs.send_window=256;evs.user_send_window=128;cert.log_conflicts=yes&quot;
##wsrep_notify_cmd='/data/galeranotify/galeranotify.py'
#wsrep_log_conflicts=1
#wsrep_forced_binlog_format=ROW
#wsrep_drupal_282555_workaround=1
#wsrep_max_ws_size=2147483647
##wsrep_dirty_reads=0 #当值是1的时候这个节点是只读节点
##wsrep_sst_method=[mysqldump|xtrabackup|xtrabackup-v2|rsync|rsync_wan]
#wsrep_sst_method=xtrabackup-v2
wsrep_sst_method=rsync
wsrep_sst_auth=tb:password

V. 启动第一台:

这里需要注意的是,第一次启动使用–wsrep-new-cluster,如果此时集群中这台故障,需要修改配置文件打开:wsrep_cluster_address=”gcomm://10.0.1.49,10.10.240.113,10.0.1.61″这一项,如过其他两天数据已经和这台不同步,这需要删除目录数据后进行同步恢复并加入集群

[root@LinuxEA /data/mysql]# /etc/init.d/mysqld start --wsrep-new-cluster

做一些简单的优化:

DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.db WHERE Db LIKE 'test%';
DROP DATABASE test;
UPDATE mysql.user SET password = password('abc123') WHERE user = 'root';

VI. 启动第二台:

第二台的mariadb安装完成后,直接删掉/data/mysql下的文件,从10.0.1.49同步

[root@LinuxEA-2 /data/mysql]# rm -rf *
[root@LinuxEA-2 /data/mysql]# /etc/init.d/mysqld start
Starting MySQL.170524 17:43:41 mysqld_safe Adding '/usr/local/lib/libjemalloc.so' to LD_PRELOAD for mysqld
170524 17:43:41 mysqld_safe Logging to '/data/mysql/mysql-error.log'.
170524 17:43:41 mysqld_safe Starting mysqld daemon with databases from /data/mysql
..........SST in progress, setting sleep higher.... SUCCESS! 

部分配置文件:

[galera]
##https://www.percona.com/doc/percona-xtradb-cluster/5.6/wsrep-system-index.html
wsrep_on=ON
wsrep_cluster_address=&quot;gcomm://10.0.1.49,10.10.240.113,10.0.1.61&quot;
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_name='cluster'
wsrep_node_address='10.0.1.61'
wsrep_node_name='node2'
#wsrep_replicate_myisam=1
#wsrep_node_name='node1'
#wsrep_replicate_myisam=1
####wsrep_slave_threads = 8
#wsrep_slave_threads = 16
##wsrep_provider_options=&quot;gcs.fc_limit=512&quot;
#wsrep_provider_options=&quot;gcache.page_size=128M;gcache.size=2G;gcs.fc_limit=512;gcs.fc_factor=0.9;evs.send_window=256;evs.user_send_window=128;cert.log_conflicts=yes&quot;
##wsrep_notify_cmd='/data/galeranotify/galeranotify.py'
#wsrep_log_conflicts=1
#wsrep_forced_binlog_format=ROW
#wsrep_drupal_282555_workaround=1
#wsrep_max_ws_size=2147483647
##wsrep_dirty_reads=0 #当值是1的时候这个节点是只读节点
##wsrep_sst_method=[mysqldump|xtrabackup|xtrabackup-v2|rsync|rsync_wan]
#wsrep_sst_method=xtrabackup-v2
wsrep_sst_method=rsync
wsrep_sst_auth=tb:password

VII. 启动第三台

同样删除/data/mysql下的文件,从10.0.1.49同步

[root@LinuxEA-3 /data/mysql]# rm -rf *
[root@LinuxEA-3 /data/mysql]# /etc/init.d/mysqld start
Starting MySQL.170524 18:58:34 mysqld_safe Adding '/usr/local/lib/libjemalloc.so' to LD_PRELOAD for mysqld
170524 18:58:34 mysqld_safe Logging to '/data/mysql/mysql-error.log'.
170524 18:58:34 mysqld_safe Starting mysqld daemon with databases from /data/mysql
.....SST in progress, setting sleep higher.. SUCCESS! 
[root@LinuxEA-3 /data/mysql]# 

部分配置文件:

[galera]
##https://www.percona.com/doc/percona-xtradb-cluster/5.6/wsrep-system-index.html
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=&quot;gcomm://10.0.1.49,10.10.240.113,10.0.1.49&quot;
#wsrep_cluster_address=&quot;gcomm://&quot;
wsrep_cluster_name='cluster'
wsrep_node_address='10.10.240.113'
wsrep_node_name='node3'
#wsrep_replicate_myisam=1
####wsrep_slave_threads = 8
#wsrep_slave_threads = 16
##wsrep_provider_options=&quot;gcs.fc_limit=512&quot;
#wsrep_provider_options=&quot;gcache.page_size=128M;gcache.size=2G;gcs.fc_limit=512;gcs.fc_factor=0.9;evs.send_window=256;evs.user_send_window=128;cert.log_conflicts=yes&quot;
##wsrep_notify_cmd='/data/galeranotify/galeranotify.py'
#wsrep_log_conflicts=1
#wsrep_forced_binlog_format=ROW
#wsrep_drupal_282555_workaround=1
#wsrep_max_ws_size=2147483647
##wsrep_dirty_reads=0 #当值是1的时候这个节点是只读节点
##wsrep_sst_method=[mysqldump|xtrabackup|xtrabackup-v2|rsync|rsync_wan]
#wsrep_sst_method=xtrabackup-v2
wsrep_sst_method=rsync
wsrep_sst_auth=tb:password

三台启动完成状态:

[root@LinuxEA /data/mysql]# mysql -e &quot;SHOW STATUS LIKE 'wsrep_cluster_size';&quot;
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+

Debian安装配置MariaDB Server

MySQL

介绍

这里我们用 MariaDB 代替 MySQL,MariaDB 数据库管理系统是 MySQL 的一个分支,主要由开源社区在维护,采用 GPL 授权许可 MariaDB 的目的是完全兼容 MySQL,包括 API 和命令行,使之能轻松成为 MySQL 的代替品。

上面的都是书面语,我觉得 MariaDB 哪里好呢,首先 MySQL 分 CE(开发版)和 EE(企业版)区分了一部分用户,而且毕竟是 Orcale 的软件了,后期发展难免会有更大的区分。MariaDB 是完全由开源社区维护的,而且在功能开发上也更开放,光说国内,阿里云就有为其赞助了一位开发者——彭立勋,帮助完善复制功能并将 AliSQL 的优良特性合并入 MariaDB,最近腾讯云也赞助了一位开发者 —— 程斌(音译),帮助完善InnoDB 功能,并合并 TXSQL。可见 MariaDB 的开发生态其实非常棒,很活跃,一片生机勃勃。

MariaDB、MySQL 这样的数据库编译非常耗时而且编译很容易出错,所以使用编译好的版本无疑是最好的,设置页非常的方便。

安装

MariaDB 的软件源由官方提供,程序的质量和安全绝对是毋庸置疑的。

本教程适用于:

Debian 8 (Jessie) 支持安装 10.0~最新
Debian 9 (stretch) 支持安装 10.1~最新

教程以 10.2 版本为例,其他版本只需将下面的 10.2 修改为 10.1 10.0 即可

apt-get install software-properties-common
apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8add-apt-repository 'deb [arch=amd64] http://mirrors.tuna.tsinghua.edu.cn/mariadb/repo/10.2/debian stretch main'

apt update
apt install mariadb-server

MariaDB 设置密码

MySQL

安装过程中,这里会提示设置数据库 Root 密码,需要连续输入两次。

设置

输入下面的命令,关闭一些不安全的设置:

mysql_secure_installation

首先输入密码,提出修改米啊嘛不修改,然后一路 y 即可。

  • 基本介绍:
    Enter current password for root (enter for none):
  • 解释:输入当前 root 用户密码,默认为空,直接回车。
    Set root password? [Y/n] y
  • 解释:要设置 root 密码吗?输入 y 表示愿意。
    Remove anonymous users? [Y/n] y
  • 解释:要移除掉匿名用户吗?输入 y 表示愿意。
    Disallow root login remotely? [Y/n] y
  • 解释:不想让 root 远程登陆吗?输入 y 表示愿意。
    Remove test database and access to it? [Y/n] y
  • 解释:要去掉 test 数据库吗?输入 y 表示愿意。
    Reload privilege tables now? [Y/n] y
  • 解释:想要重新加载权限吗?输入 y 表示愿意。

管理

systemctl restart mysql #重启
systemctl start mysql #启动
systemctl stop mysql #关闭
systemctl status mysql #检查状态

更新

运行下面的命令系统就会更新所有可以更新的软件包括 MariaDB

apt update
apt upgrade -y

配置MariaDB允许客户端远程连接

1、登陆mysql数据库

mysql -u root -p

查看user表

mysql> use mysql;
Database changed
mysql> select host,user,password from user;
+--------------+------+-------------------------------------------+
| host | user | password |
+--------------+------+-------------------------------------------+
| localhost | root | A731AEBFB621E354CD41BAF207D884A609E81F5E |
| 192.168.1.1 | root | A731AEBFB621E354CD41BAF207D884A609E81F5E |
+--------------+------+-------------------------------------------+
2 rows in set (0.00 sec)

可以看到在user表中已创建的root用户。host字段表示登录的主机,其值可以用IP,也可用主机名,

(1)有时想用本地IP登录,那么可以将以上的Host值改为自己的Ip即可

2、实现远程连接(授权法)

将host字段的值改为%就表示在任何客户端机器上能以root用户登录到mysql服务器,建议在开发时设为%。

update user set host = ’%’ where user = ’root’;

将权限改为ALL PRIVILEGES

mysql> use mysql;
Database changed
mysql> grant all privileges on . to root@'%' identified by "root";
Query OK, 0 rows affected (0.00 sec)
mysql>
+--------------+------+-------------------------------------------+
| host | user | password |
+--------------+------+-------------------------------------------+
| localhost | root | A731AEBFB621E354CD41BAF207D884A609E81F5E |
| 192.168.1.1 | root | A731AEBFB621E354CD41BAF207D884A609E81F5E |
| % | root | *A731AEBFB621E354CD41BAF207D884A609E81F5E |
+--------------+------+-------------------------------------------+
3 rows in set (0.00 sec)

这样机器就可以以用户名root密码root远程访问该机器上的MySql.

3、实现远程连接(改表法)

use mysql;
update user set host = '%' where user = 'root';

这样在远端就可以通过root用户访问Mysql.

CentOS7 YUM安装MariaDB

https://downloads.mariadb.org/mariadb/repositories/#mirror=tuna&distro=CentOS&distro_release=centos7-amd64–centos7&version=10.2

1. 配置Yum安装库

Here is your custom MariaDB YUM repository entry for CentOS. Copy and paste it into a file under /etc/yum.repos.d/ (we suggest naming the file MariaDB.repo or something similar).

#MariaDB 10.2 CentOS repository list - created 2017-06-12 03:20 UTC
#http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-
gpgcheck=1

2.执行yum安装

After the file is in place, install MariaDB with:

sudo yum install MariaDB-server MariaDB-client

3.启动MariaDB

systemctl start mariadb

4.设置root用户密码

mysqladmin -u root -p password newpassword

后面的 newpassword是要设置的密码

编译支持mysql-5.1.73版本的xtrabackup

一、基础介绍

mysql5.1在源码中配备了两个版本的innodb存储引擎源码:innobase和innodb_plugin,编译安装的时候可以通过参数–with-plugins=innobase,innodb_plugin来指定是否将innodb存储引擎引入,具体这两个参数引入对编译后的mysql产生怎样的差异,后面再做解析。然而对于PerconaXtraBackup,在release版本中有2.0,2.1,2.2三个大版本,然后每个版本都是与mysql发布的innodb存储引擎版本对应,举例来说:

percona-xtrabackup-2.0.8在BUILD.txt中指定了utils/build.sh参数如下:

The script needs the codebase for which the building is targeted, you must
provide it with one of the following values or aliases:

  ================== =========  =============================================
  Value              Alias      Server
  ================== =========  =============================================
  innodb51_builtin   5.1    build against built-in InnoDB in MySQL 5.1
  innodb51           plugin build against InnoDB plugin in MySQL 5.1
  innodb55           5.5    build against InnoDB in MySQL 5.5
  xtradb51           xtradb     build against Percona Server with XtraDB 5.1
  xtradb55           xtradb55   build against Percona Server with XtraDB 5.5
  innodb56           5.6        build against InnoDB in MySQL 5.6
  ================== =========  =============================================

而在percona-xtrabackup-2.1.9在BUILD.txt中指定了utils/build.sh参数如下:

The script needs the codebase for which the building is targeted, you must
provide it with one of the following values or aliases:

  ================== =========  =============================================
  Value              Alias      Server
  ================== =========  =============================================
  innodb51           plugin build against InnoDB plugin in MySQL 5.1
  innodb55           5.5    build against InnoDB in MySQL 5.5
  xtradb51           xtradb     build against Percona Server with XtraDB 5.1
  xtradb55           xtradb55   build against Percona Server with XtraDB 5.5
  innodb56           5.6        build against InnoDB in MySQL 5.6
  ================== =========  =============================================

解释一下:从percona-xtrabackup2.1开始取消对innodb built-in版本支持,即:用Percona-Xtrabackup2.1备份5.1built-in版本将会出现如下报错:

innobackupex: Error: Support for MySQL 5.1 with builtin InnoDB (not the plugin) was removed in Percona XtraBackup 2.1. The last version to support MySQL 5.1 with builtin InnoDB was Percona XtraBackup 2.0.

二、Percona-Xtrabackup2.0.8编译安装

cd /tmp
wget https://github.com/percona/percona-xtrabackup/archive/percona-xtrabackup-2.0.8.tar.gz
tar xf percona-xtrabackup-2.0.8.tar.gz
cd percona-xtrabackup-percona-xtrabackup-2.0.8
wget http://downloads.mysql.com/archives/mysql-5.1/mysql-5.1.59.tar.gz 
./utils/build.sh 5.1

编译安装完,备份工具保存在 src/xtrabackup_51,打包xtrabackup_51和innobackupex 到目的服务器的/usr/local/bin目录下,即可执行备份

centos 7 yum安装mysql 5.7

1 查看Linux发行版本

[root@typecodes ~]# cat /etc/redhat-release
CentOS Linux release 7.2.1511 (Core)

2 下载MySQL官方的Yum Repository

根据Linux发行版本(CentOS、Fedora都属于红帽系),从mysql官方(http://dev.mysql.com/downloads/repo/yum/)获取Yum Repository。

[root@typecodes ~]#  wget -i http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm
--2016-02-03 18:36:02--  http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm
Resolving dev.mysql.com (dev.mysql.com)... 137.254.60.11
Connecting to dev.mysql.com (dev.mysql.com)|137.254.60.11|:80... connected.
HTTP request sent, awaiting response... 302 Found
Location: http://repo.mysql.com//mysql57-community-release-el7-7.noarch.rpm [following]
--2016-02-03 18:36:04--  http://repo.mysql.com//mysql57-community-release-el7-7.noarch.rpm
Resolving repo.mysql.com (repo.mysql.com)... 104.102.164.25
Connecting to repo.mysql.com (repo.mysql.com)|104.102.164.25|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 8984 (8.8K) [application/x-redhat-package-manager]
Saving to: ‘mysql57-community-release-el7-7.noarch.rpm’

100%[=============================================================================================================================>] 8,984       --.-K/s   in 0s

2016-02-03 18:36:07 (68.4 MB/s) - ‘mysql57-community-release-el7-7.noarch.rpm’ saved [8984/8984]

3 安装MySQL的Yum Repository

安装完MySQL的Yum Repository,每次执行yum update都会检查MySQL是否更新。

[root@typecodes ~]# yum -y install mysql57-community-release-el7-7.noarch.rpm
Loaded plugins: axelget, fastestmirror, langpacks
Examining mysql57-community-release-el7-7.noarch.rpm: mysql57-community-release-el7-7.noarch
Marking mysql57-community-release-el7-7.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mysql57-community-release.noarch 0:el7-7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=======================================================================================================================================================================
 Package                                       Arch                       Version                    Repository                                                   Size
=======================================================================================================================================================================
Installing:
 mysql57-community-release                     noarch                     el7-7                      /mysql57-community-release-el7-7.noarch                     7.8 k

Transaction Summary
=======================================================================================================================================================================
Install  1 Package

Total size: 7.8 k
Installed size: 7.8 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql57-community-release-el7-7.noarch                                                                                                              1/1 
  Verifying  : mysql57-community-release-el7-7.noarch                                                                                                              1/1

Installed:
  mysql57-community-release.noarch 0:el7-7

Complete!

4 安装MySQL数据库的服务器版本

[root@typecodes ~]# yum -y install mysql-community-server
Loaded plugins: axelget, fastestmirror, langpacks
No metadata available for base
No metadata available for epel
No metadata available for extras
repomd.xml                                                                                                                                      | 2.5 kB  00:00:00     
update mysql-connectors-community metadata successfully
repomd.xml                                                                                                                                      | 2.5 kB  00:00:00     
update mysql-tools-community metadata successfully
repomd.xml                                                                                                                                      | 2.5 kB  00:00:00     
update mysql57-community metadata successfully
No metadata available for updates
mysql-connectors-community                                                                                                                      | 2.5 kB  00:00:00     
mysql-tools-community                                                                                                                           | 2.5 kB  00:00:00     
mysql57-community                                                                                                                               | 2.5 kB  00:00:00     
(1/3): mysql-tools-community/x86_64/primary_db                                                                                                  |  24 kB  00:00:01     
(2/3): mysql57-community/x86_64/primary_db                                                                                                      |  28 kB  00:00:01     
(3/3): mysql-connectors-community/x86_64/primary_db                                                                                             | 8.6 kB  00:00:02     
Loading mirror speeds from cached hostfile
 * base: mirrors.aliyun.com
 * epel: mirrors.neusoft.edu.cn
 * extras: mirrors.163.com
 * updates: mirrors.163.com
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-server.x86_64 0:5.7.10-1.el7 will be installed
--> Processing Dependency: mysql-community-common(x86-64) = 5.7.10-1.el7 for package: mysql-community-server-5.7.10-1.el7.x86_64
--> Processing Dependency: mysql-community-client(x86-64) = 5.7.10-1.el7 for package: mysql-community-server-5.7.10-1.el7.x86_64
--> Running transaction check
---> Package mysql-community-client.x86_64 0:5.7.10-1.el7 will be installed
--> Processing Dependency: mysql-community-libs(x86-64) = 5.7.10-1.el7 for package: mysql-community-client-5.7.10-1.el7.x86_64
---> Package mysql-community-common.x86_64 0:5.7.10-1.el7 will be installed
--> Running transaction check
---> Package mariadb-libs.x86_64 1:5.5.44-2.el7.centos will be obsoleted
--> Processing Dependency: libmysqlclient.so.18()(64bit) for package: 2:postfix-2.10.1-6.el7.x86_64
--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: 2:postfix-2.10.1-6.el7.x86_64
---> Package mysql-community-libs.x86_64 0:5.7.10-1.el7 will be obsoleting
--> Running transaction check
---> Package mysql-community-libs-compat.x86_64 0:5.7.10-1.el7 will be obsoleting
--> Finished Dependency Resolution

Dependencies Resolved

=======================================================================================================================================================================
 Package                                            Arch                          Version                               Repository                                Size
=======================================================================================================================================================================
Installing:
 mysql-community-libs                               x86_64                        5.7.10-1.el7                          mysql57-community                        2.2 M
     replacing  mariadb-libs.x86_64 1:5.5.44-2.el7.centos
 mysql-community-libs-compat                        x86_64                        5.7.10-1.el7                          mysql57-community                        2.0 M
     replacing  mariadb-libs.x86_64 1:5.5.44-2.el7.centos
 mysql-community-server                             x86_64                        5.7.10-1.el7                          mysql57-community                        142 M
Installing for dependencies:
 mysql-community-client                             x86_64                        5.7.10-1.el7                          mysql57-community                         24 M
 mysql-community-common                             x86_64                        5.7.10-1.el7                          mysql57-community                        269 k

Transaction Summary
=======================================================================================================================================================================
Install  3 Packages (+2 Dependent packages)

Total download size: 171 M
Downloading packages:
mysql-community-server-5.7.10-1.el7.x86_64.rpm                                                                                                  | 142 MB  00:03:42     
mysql-community-libs-compat-5.7.10-1.el7.x86_64.rpm                                                                                             | 2.0 MB  00:00:03     
mysql-community-libs-5.7.10-1.el7.x86_64.rpm                                                                                                    | 2.2 MB  00:00:05     
mysql-community-client-5.7.10-1.el7.x86_64.rpm                                                                                                  |  24 MB  00:00:41     
warning: /var/cache/yum/x86_64/7/mysql57-community/packages/mysql-community-common-5.7.10-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY00 ETA 
Public key for mysql-community-common-5.7.10-1.el7.x86_64.rpm is not installed
mysql-community-common-5.7.10-1.el7.x86_64.rpm                                                                                                  | 269 kB  00:00:01     
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Importing GPG key 0x5072E1F5:
 Userid     : "MySQL Release Engineering <[email protected]>"
 Fingerprint: a4a9 4068 76fc bd3c 4567 70c8 8c71 8d3b 5072 e1f5
 Package    : mysql57-community-release-el7-7.noarch (@/mysql57-community-release-el7-7.noarch)
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql-community-common-5.7.10-1.el7.x86_64                                                                                                          1/6 
  Installing : mysql-community-libs-5.7.10-1.el7.x86_64                                                                                                            2/6 
  Installing : mysql-community-client-5.7.10-1.el7.x86_64                                                                                                          3/6 
  Installing : mysql-community-server-5.7.10-1.el7.x86_64                                                                                                          4/6 
  Installing : mysql-community-libs-compat-5.7.10-1.el7.x86_64                                                                                                     5/6 
  Erasing    : 1:mariadb-libs-5.5.44-2.el7.centos.x86_64                                                                                                           6/6 
  Verifying  : mysql-community-server-5.7.10-1.el7.x86_64                                                                                                          1/6 
  Verifying  : mysql-community-libs-compat-5.7.10-1.el7.x86_64                                                                                                     2/6 
  Verifying  : mysql-community-common-5.7.10-1.el7.x86_64                                                                                                          3/6 
  Verifying  : mysql-community-libs-5.7.10-1.el7.x86_64                                                                                                            4/6 
  Verifying  : mysql-community-client-5.7.10-1.el7.x86_64                                                                                                          5/6 
  Verifying  : 1:mariadb-libs-5.5.44-2.el7.centos.x86_64                                                                                                           6/6

Installed:
  mysql-community-libs.x86_64 0:5.7.10-1.el7          mysql-community-libs-compat.x86_64 0:5.7.10-1.el7          mysql-community-server.x86_64 0:5.7.10-1.el7

Dependency Installed:
  mysql-community-client.x86_64 0:5.7.10-1.el7                                       mysql-community-common.x86_64 0:5.7.10-1.el7

Replaced:
  mariadb-libs.x86_64 1:5.5.44-2.el7.centos

Complete!
[root@typecodes ~]# clear

最后的截图:

MySQL

5 启动数据库:

[root@typecodes ~]# systemctl start  mysqld.service

然后使用命令systemctl status mysql.service查看MySQL数据库启动后的服务状态:

MySQL

6 获取初始密码

使用YUM安装并启动MySQL服务后,MySQL进程会自动在进程日志中打印root用户的初始密码:

#######从mysql进程日志中获取root用户的初始密码:ra%yk7urCBIh
[root@typecodes ~]# grep "password" /var/log/mysqld.log
2016-02-03T10:42:17.272166Z 1 [Note] A temporary password is generated for root@localhost: ra%yk7urCBIh
2016-02-03T10:42:36.776875Z 2 [Note] Access denied for user 'root'@'localhost' (using password: NO)
2016-02-03T10:42:52.063138Z 3 [Note] Access denied for user 'root'@'localhost' (using password: NO)
2016-02-03T10:42:57.564373Z 4 [Note] Access denied for user 'root'@'localhost' (using password: NO)
2016-02-03T10:43:01.477007Z 5 [Note] Access denied for user 'root'@'localhost' (using password: NO)
2016-02-03T10:46:03.642008Z 6 [Note] Access denied for user 'root'@'localhost' (using password: NO)
2016-02-03T10:46:11.217889Z 7 [Note] Access denied for user 'root'@'localhost' (using password: NO)
2016-02-03T10:47:44.755199Z 0 [Note] Shutting down plugin 'validate_password'
2016-02-03T10:47:46.505844Z 0 [Note] Shutting down plugin 'sha256_password'
2016-02-03T10:47:46.505851Z 0 [Note] Shutting down plugin 'mysql_native_password'

7 修改root用户密码

使用小节5中获取的root用户的初始密码,然后进行修改:

[root@typecodes ~]# mysql -uroot -p
Enter password:             #######输入默认的root密码后回车
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.7.10

Copyright (c) 2000, 2015, 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> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new password';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

8 安装完毕

至此,使用在CentOS7中使用YUM方法安装MySQL5.7.10数据库完毕。如下所示,可以使用新的root密码登陆MySQL了。

[root@typecodes ~]# mysql -uroot -ppassword
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.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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> 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

Database changed
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> clear
mysql> exit
Bye
[root@typecodes ~]# clear

附录1:查看MySQL数据库的配置信息

MySQL的配置文件依然是/etc/my.cnf,其它安装信息可以通过mysql_config命令查看。其中,动态库文件存放在/usr/lib64/mysql目录下。

MySQL

附录2:对于C/C++等开发者

由于需要用到类似mysql.h等头文件,需要执行下面的命令安装mysql开发版本即可。

[root@typecodes ~]# yum -y install mysql-community-devel

附录3:删除MySQL的Repository

因为小节3中安装了MySQL的Yum Repository,所以以后每次执行yum操作时,都会去检查更新。如果想要去掉这种自动检查操作的话,可以使用如下命令卸载MySQL的Repository即可。

[root@typecodes ~]# yum -y remove mysql57-community-release-el7-7.noarch