标签归档:MySQL

MySQL自动备份并提交到码云Git仓库

之所以选择码云是因为其私有仓库是免费的,而GitHub私有仓库是收费,约7+美元一月,对于个人开发者似乎也是一笔不小的费用。

在创建自动将备份上传到码云Git仓库前,建议您先看下以下文章:

如何通过Git将本地项目推送到码云或GitHub

对于不太了解Git的朋友来说,此文章非常有用,否则在进行以下步骤时会出现各种报错。

实现方案:

    • 登录到码云
    • 创建私有仓库
    • 服务器生成ssh公钥
    • 创建SSH公钥

以上步骤在上文中有详细说明。

1.创建shell脚本文件:

$ cd /mnt/renwole
$ vim mysqlbak.sh

2.添加以下内容:

#!/bin/bash
createAt=`date +%Y-%m-%d-%H:%M:%S`
mysql_back_path=/mnt/MySQL-Bak
/usr/local/mysql/bin/mysqldump -u数据库用户名 -p密码 数据库名 > $mysql_back_path/renwoleblog-$createAt.sql
# 自动删除7天前的备份
# -type 文件类型 f是文件
find $mysql_back_path -name "*.sql" -type f -mtime +7 -exec rm -rf {} \;
cd $mysql_back_path
git add -A
git commit -m "${createAt}"
git push origin master

注意:上面的路径一定要是绝对路径,否则执行crontabmysqldump成功,但是导出结果为空,手动执行正常!

3.设置执行权限:

$ chmod +x mysqlbak.sh

4.添加任务计划:

通过crontab定时执行备份脚本

$ crontab -e

插入如下内容:

*/50 2 * * * /mnt/renwole/mysqlbak.sh
$ systemctl restart crond

设置完成后,每日凌晨2点50分,自动备份mysql数据库并推送到码云git仓库,后期可以通过Git查看历史提交的版本。再也不用担心数据丢失了。

优化并修复 MariaDB(MySQL)数据库损坏的表文件

平时维护网站的时候一定会遇到数据表崩溃,这可能是MySQL没有正常关闭或断电等原因导致。这时就需要我们手动修复表文件,通常可以通过数据库管理工具,例如:phpmyadmin 进行可视化修复。下面我讲解在Linux下通过命令修复损坏的表文件。

以下操作以本博客使用的 WordPress 程序演示,如果部分表存在问题则可以修复或优化。

登录MariaDB数据库

$ mysql -u root -p
Enter password:[输入您的mysql或管理其数据库的密码]
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 84162
Server version: 10.3-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use renwolecomdb;

查看当前所有数据表文件:

MariaDB [renwolecomdb]> show tables;
+------------------------+
| Tables_in_renwolecomdb  |
+------------------------+
| wp_baidusubmit_sitemap |
| wp_baidusubmit_urlstat |
| wp_commentmeta         |
| wp_comments            |
| wp_links               |
| wp_options             |
| wp_postmeta            |
| wp_posts               |
| wp_term_relationships  |
| wp_term_taxonomy       |
| wp_termmeta            |
| wp_terms               |
| wp_usermeta            |
| wp_users               |
+------------------------+
14 rows in set (0.00 sec)

如果感觉您的数据库某个表加载较慢,可以进行数据表优化,命令如下:

MariaDB [renwolecomdb]> OPTIMIZE TABLE wp_baidusubmit_urlstat;
+------------------------------------+----------+----------+----------+
| Table                              | Op       | Msg_type | Msg_text |
+------------------------------------+----------+----------+----------+
|renwolecomdb.wp_baidusubmit_urlstat | optimize | status   | OK       |
+------------------------------------+----------+----------+----------+
1 row in set (0.11 sec)

上面已打印出详细的分析报告。

下面将修复被破坏的表文件,命令如下:

MariaDB [renwolecomdb]> REPAIR TABLE wp_baidusubmit_sitemap;
+------------------------------------+--------+----------+----------+
| Table                              | Op     | Msg_type | Msg_text |
+------------------------------------+--------+----------+----------+
|renwolecomdb.wp_baidusubmit_sitemap | repair | status   | OK       |
+------------------------------------+--------+----------+----------+
1 row in set (0.00 sec)

修复成功。

修复 InnoDB 引擎的表文件,命令如下:

MariaDB [renwolecomdb]> REPAIR TABLE wp_commentmeta;
+----------------------------+--------+----------+---------------------------------------------------------+
| Table                      | Op     | Msg_type | Msg_text                                                |
+----------------------------+--------+----------+---------------------------------------------------------+
|renwolecomdb.wp_commentmeta | repair | note     | The storage engine for the table doesn't support repair |
+----------------------------+--------+----------+---------------------------------------------------------+
1 row in set (0.00 sec)

提示错误报告:The storage engine for the table doesn't support repair.

上述报错说明该数据库InnoDB引擎数据表不支持修复。

但需要说明的是:

InnoDB损坏表的几率非常小,因为InnoDB是先写入日志,再写入数据库,因此InnoDB引擎比MyISAM健壮很多,而且他有自我修复能力,一般情况一旦InnoDB的数据文件损坏,只能找备份恢复。

所以我们平时做运维的一定要对数据备份、备份、再备份,以防万一。

MySQL 5.8(MariaDB)启动后立即停止解决方案

安装一切都很顺利,除了最后一步,启动mysql:

$ mysqld –skip-grant-tables –user=mysql

打印出以下错误信息,然后又停止了:

[ERROR] Can’t find messagefile ‘/usr/share/errmsg.sys’

在谷歌搜索之后,我找到了添加以下参数启动的方法:

–lc-messages-dir=”/usr/local/mysql/share/english/

为了启动它并让它实际工作,我使用了以下命令:

$ mysqld –skip-grant-tables –user=mysql –lc-messages-dir=”/usr/local/mysql/share/english/

最后成功启动。

Zabbix 3.4 监控 MariaDB(MySQL) 数据库性能

先决条件:

首先你需要安装 Zabbix Servers 及 Zabbix-agentd 端:

Zabbix Servers 3.4 源代码编译安装
Centos 7 Zabbix Agent 客户端源码编译安装配置

1.配置 userparameter_mysql.conf

Zabbix源码中包含了 userparameter_mysql.conf 配置文件,只需要将其拷贝到zabbix_agentd.conf.d目录下:

$ find / -name userparameter_mysql.conf
$ cd /tmp/zabbix-3.4.2/conf/zabbix_agentd/
$ cp userparameter_mysql.conf /usr/local/zabbix/etc/zabbix_agentd.conf.d/

2.修改 zabbix_agentd.conf

因为我们将userparameter_mysql.conf文件放在了zabbix_agentd.conf.d目录,所以需要取消下面注释,这样Zabbix Agent才能够加载此文件

$ vim /usr/local/zabbix/etc/zabbix_agentd.conf
...
Include=/usr/local/zabbix/etc/zabbix_agentd.conf.d/*.conf
...

注意:若 systemctl Zabbix-Agent.service 单元文件中已经加载此配置文件/路径,此步骤可以忽略。

3.创建监控 MariaDB(MySQL) 用户信息

MariaDB [(none)]> GRANT SELECT ON *.* TO 'zabbix'@'localhost' IDENTIFIED BY 'Renwolecom';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> exit
Bye

注意:MySQL用户权限可根据需求设置,可参考《Linux MariaDB(MySQL)数据库更改用户权限》。

4.创建 .my.cnf 文件,添加 MariaDB(MySQL) 用户信息

$ vim /usr/local/zabbix/etc/.my.cnf

# Zabbix Agent
[mysql]
host=localhost
user=zabbix
password="Renwolecom"
socket=/tmp/mysql.sock
[mysqladmin]
host=localhost
user=zabbix
password="Renwolecom"
socket=/tmp/mysql.sock

5.修改 userparameter_mysql.conf

修改后的内容如下:

$ cd /usr/local/zabbix/etc/zabbix_agentd.conf.d/
$ egrep -v "(^#|^$)" userparameter_mysql.conf

UserParameter=mysql.status[*],echo "show global status where Variable_name='$1';" | HOME=/usr/local/zabbix/etc/ /usr/local/mysql/bin/mysql -N | awk '{print $$2}'
UserParameter=mysql.size[*],bash -c 'echo "select sum($(case "$3" in both|"") echo "data_length+index_length";; data|index) echo "$3_length";; free) echo "data_free";; esac)) from information_schema.tables$([[ "$1" = "all" || ! "$1" ]] || echo " where table_schema=\"$1\"")$([[ "$2" = "all" || ! "$2" ]] || echo "and table_name=\"$2\"");" | HOME=/usr/local/zabbix/etc mysql -N'
UserParameter=mysql.ping,HOME=/usr/local/zabbix/etc/ /usr/local/mysql/bin/mysqladmin ping | grep -c alive
UserParameter=mysql.version,/usr/local/mysql/bin/mysql -V
UserParameter=mysql.vars[*],echo "show variables where Variable_name='$1';" | HOME=/usr/local/zabbix/etc/ /usr/local/mysql/bin/mysql -N | awk '{print $$2}'

注:以上内容请注意相关绝对路径。

6.重启 Zabbix-Agent 服务

$ systemctl restart zabbix-agent

最后在 Zabbix UI界面中添加MySQL监控模板即可。

Zabbix Servers 3.4 源代码编译安装

PS:

其实安装zabbix很简单,网上资料一搜一大把,基本都是采用yum方式安装,简单省事。正因如此我没写相关配置文档,安装方式一般用yum安装或源码安装亦或是容器安装,最近有网友提出来了,我认真对待,撰写 Linux Centos 7 Zabbix 编译安装的全过程(Zabbix服务端)。后期我也会写一些关于Zabbix 使用及优化方面的文章。

官介:

Zabbix是一个企业级的分布式开源监控解决方案。它可以监控各种网络应用以及服务器健康性和完整性的软件。Zabbix服务器将其数据存储在由MySQL、MariaDB、PostgreSQL或Oracle支持的关系型数据库中,Zabbix所有的报告、统计信息和配置参数都可以通过基于Web的前端页面进行访问设置。而且Zabbix是开源免费的。

1.安装环境(Hyper-V 虚拟机):

$ hostnamectl
 Static hostname: localhost.localdomain
 Icon name: computer-vm
 Chassis: vm
 Machine ID: renwoles1d8743989a40cb81db696400
 Boot ID: renwoles272f4aa59935dcdd0d456501
 Virtualization: microsoft
 Operating System: CentOS Linux 7 (Core)
 CPE OS Name: cpe:/o:centos:centos:7
 Kernel: Linux 3.10.0-514.2.2.el7.x86_64
 Architecture: x86-64

2.由于Zabbix UI前端使用 PHP 写的,所以必须运行在支持PHP脚本语言的Web服务器,先决条件:

Centos 7 编译安装nginx
Centos 7 编译安装PHP 7 之生产篇
Linux JAVA JDK JRE 环境变量安装与配置
Centos 7 二进制安装 MariaDB(MySQL)数据库

3.若您的服务器已具备 PHP 环境,请略过第 2 步骤,继续以下配置:

配置 php.ini 必须满足以下要求:

max_execution_time = 300
max_input_time = 300
memory_limit = 128M
post_max_size = 32M
date.timezone = Asia/Shanghai
mbstring.func_overload=2
#Zabbix PHP LDAP 认证开启(可选)
extension = "/usr/local/php/lib/php/extensions/no-debug-zts-20160303/ldap.so"

4.安装依赖包并创建组&用户

$ yum -y install epel-release && yum -y update
$ yum install -y net-snmp-devel OpenIPMI-devel libssh2-devel iksemel-devel
$ groupadd zabbix
$ useradd -g zabbix zabbix

5.安装 Zabbix Server

下载地址://www.zabbix.com/download

$ cd /tmp
$ tar -zxvf zabbix-3.4.2.tar.gz
$ cd zabbix-3.4.2
$ ./configure --prefix=/usr/local/zabbix \
--sysconfdir=/usr/local/zabbix/etc \
--enable-server \
--enable-agent \
--with-mysql=/usr/bin/mysql_config \
--with-net-snmp \
--with-libcurl \
--with-libxml2 \
--enable-proxy \
--enable-ipv6 \
--enable-java \
--with-ssh2 \
--with-iconv \
--with-openipmi \
--with-ldap \
--with-openssl \
--with-jabber \

$ make
$ make install

注意:–enable-agent 可选,此参数主要用于客户端(被监控者)。

注意:如你未安装JAVA环境,请去掉 –enable-java 选项,主要用于监控Tomcat。

6.创建并赋予 Zabbix 配置文件目录权限

$ mkdir -p /usr/local/zabbix/logs
$ chown -R zabbix.zabbix /usr/local/zabbix

7.将 Zabbix UI 前端程序源码拷贝到 Nginx Web 目录

$ cp -rf /tmp/zabbix-3.4.2/frontends/php/* /apps/web/zabbix
$ chown -R www.www /apps/web/zabbix

8.创建数据库名:zabbix 用户:zabbix 密码:renwolecom 并赋予用户拥有该数据库所有权限:

$ mysql -uroot -p
Enter password:(输入root数据库密码回车)
MariaDB [(none)]> create database zabbix character set utf8 collate utf8_bin;
MariaDB [(none)]> grant all privileges on zabbix.* to zabbix@"%" identified by 'renwolecom';
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> quit;

9.将Zabbix相关结构表文件按顺序依次导入zabbix数据库

导入顺序 schema.sql 表结构 > images.sql 图片库 > data.sql 初始脚本

$ cd /tmp/zabbix-3.4.2/database/mysql
$ mysql -uzabbix -prenwolecom zabbix < schema.sql
$ mysql -uzabbix -prenwolecom zabbix < images.sql
$ mysql -uzabbix -prenwolecom zabbix < data.sql

10.配置 zabbix_server.conf

配置完成后的内容如下:

$ egrep -v "^$|^#|^;" /usr/local/zabbix/etc/zabbix_server.conf
LogFile=/usr/local/zabbix/logs/zabbix_server.log
PidFile=/usr/local/zabbix/zabbix_server.pid
LogFileSize=0
DBHost=10.10.204.62
DBName=zabbix
DBUser=zabbix
DBPassword=renwolecom
JavaGateway=10.10.204.62
JavaGatewayPort=10052
StartJavaPollers=5
StartPingers=4
Timeout=4
AlertScriptsPath=/usr/local/zabbix/share/zabbix/alertscripts
LogSlowQueries=3000
Include=/usr/local/zabbix/etc/zabbix_server.conf.d/

注意:由于每个生产环境的参数配置不同,所以配置文件我只列出基本常用的,查看更多配置参数是说明

11.创建 systemctl 系统 Zabbix servers 单元文件

$ vim /usr/lib/systemd/system/zabbix.service
[Unit]
Description=Zabbix Server
After=syslog.target
After=network.target

[Service]
Environment="CONFFILE=/usr/local/zabbix/etc/zabbix_server.conf"
EnvironmentFile=-/usr/local/zabbix/etc/zabbix_server.conf.d
Type=forking
Restart=on-failure
PIDFile=/usr/local/zabbix/zabbix_server.pid
KillMode=control-group
ExecStart=/usr/local/zabbix/sbin/zabbix_server -c $CONFFILE
ExecStop=/bin/kill -SIGTERM $MAINPID
RestartSec=10s
TimeoutSec=0

[Install]
WantedBy=multi-user.target

12.启动 zabbix 并加入开机自起动

$ systemctl start zabbix
$ systemctl enable zabbix

13.Firewalld 防火墙设置

$ firewall-cmd --permanent --add-port=10051/tcp
$ firewall-cmd --permanent --add-port=80/tcp
$ firewall-cmd --reload

安装完成。

接下来,你可以使用 IP 或域名访问 Zabbix 监控管理系统,具体设置,请根据 Zabbix Server Web 向导提示设置即可。

设置完成后,zabbix默认账号:Admin 密码:zabbix(注意账号区分大小写)登录后是英文界面, 只需点击右上角的小人图标(当前用户设置)定位到 Language 项,选择 Chinese (zh_CN),点击 Update 即可切换为中文界面。

关于Zabbix代理客户端,请参阅《Centos 7 Zabbix Agent 客户端源码编译安装配置》。

MariaDB MaxScale 2.1.3中间件数据库读写分离安装配置

操作系统:CentOS Linux release 7.3.1611 (Core)
数据库:MariaDB-10.2.6-linux-glibc_214-x86_64
MaxScale服务器:10.200.10.55
主服务器:172.16.8.56
从服务器:172.16.8.57
从服务器:172.16.8.58

1.maxscale的安装方式有很多,例如源码安装、rpm、二进制构建等,我选择二进制进行安装。

根据场景需要下载相对应的版本,下载地址;//mariadb.com/downloads/maxscale

[root@localhost ~]# groupadd maxscale
[root@localhost ~]# useradd -g maxscale maxscale
[root@localhost ~]# cd /usr/local
[root@localhost local]# wget //downloads.mariadb.com/MaxScale/2.1.3/centos/7server/x86_64/maxscale-2.1.3.centos.7.tar.gz
[root@localhost local]# tar zxvf maxscale-2.1.3.centos.7.tar.gz
[root@localhost local]# ln -s maxscale-2.1.3.centos.7 maxscale
[root@localhost local]# cd maxscale
[root@zhu56 maxscale]# chown -R maxscale var

建议创建软连接,这样有助于以后的版本升级及后期维护。

2.首次安装maxscale需要创建日志相关目录

[root@localhost ~]# mkdir /var/log/maxscale
[root@localhost ~]# mkdir /var/lib/maxscale
[root@localhost ~]# mkdir /var/run/maxscale
[root@localhost ~]# mkdir /var/cache/maxscale

3.以下目录必须具备maxscala用户权限

[root@localhost ~]# chown maxscale /var/log/maxscale
[root@localhost ~]# chown maxscale /var/lib/maxscale
[root@localhost ~]# chown maxscale /var/run/maxscale
[root@localhost ~]# chown maxscale /var/cache/maxscale

4.为了能让Maxscale能顺利启动,还需要创建配置文件,在Maxscale目录下有配置文件模板拷贝到etc下即可。

[root@localhost ~]# cp /usr/local/maxscale/etc/maxscale.cnf.template /etc/maxscale.cnf

5.在修改配置文件之前,需要在主服务器上创建一个用户并给予授权,而这个用户用于MySQL监控、路由功能

MariaDB [(none)]> create user 'jiankongdb'@'%' identified by 'jiankong123';
MariaDB [(none)]> grant SELECT on mysql.user to 'jiankongdb'@'%';
MariaDB [(none)]> GRANT SELECT ON mysql.db TO 'jiankongdb'@'%';
MariaDB [(none)]> GRANT SELECT ON mysql.tables_priv TO 'jiankongdb'@'%';
MariaDB [(none)]> GRANT SHOW DATABASES ON *.* TO 'jiankongdb'@'%';
MariaDB [(none)]> grant REPLICATION CLIENT on *.* to 'jiankongdb'@'%';

MariaDB [(none)]> GRANT replication slave, replication client,SELECT ON *.* TO jiankongdb@'%';

6.查看授权情况

MariaDB [(none)]> SHOW GRANTS FOR'jiankong'@'%';

7.接下来就开始修改maxscale.cnf配置文件,否则无法启动。

[root@localhost ~]# vim /etc/maxscale.cnf

# MaxScale documentation on GitHub:
# //github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Documentation-Contents.md

# Global parameters
#
# Complete list of configuration options:
# //github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Getting-Started/Configuration-Guide.md
#全局配置
[maxscale]
threads=1

# Server definitions
#
# Set the address of the server to the network
# address of a MySQL server.
#

[server1]
type=server
address=172.16.8.56
port=3306
protocol=MySQLBackend
serv_weight=1

[server2]
type=server
address=172.16.8.57
port=3306
protocol=MySQLBackend
serv_weight=3

[server3]
type=server
address=172.16.8.58
port=3306
protocol=MySQLBackend
serv_weight=3


# Monitor for the servers
#
# This will keep MaxScale aware of the state of the servers.
# MySQL Monitor documentation:
# //github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Monitors/MySQL-Monitor.md
#MariaDB状态监控
[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2,server3
user=jiankong
passwd=jiankong123
monitor_interval=10000
detect_stale_master=true #即使从全挂掉,保证主担任读写

# Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
#

# ReadConnRoute documentation:
# //github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Routers/ReadConnRoute.md
#读
[Read-Only Service]
type=service
router=readconnroute
servers=server1,server2,server3
user=jiankong
passwd=jiankong123
router_options=slave
enable_root_user=1 #允许root用户登录执行
weightby=serv_weight #主从权重

# ReadWriteSplit documentation:
# //github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Routers/ReadWriteSplit.md
#写
[Read-Write Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=jiankong
passwd=jiankong123
max_slave_connections=100%
use_sql_variables_in=master #保证会话的一致性
enable_root_user=1 #允许root登录
max_slave_replication_lag=3600 #允许从超出主的同步时间,超出则不路由

# This service enables the use of the MaxAdmin interface
# MaxScale administration guide:
# //github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Reference/MaxAdmin.md

[MaxAdmin Service]
type=service
router=cli

# Listener definitions for the services
#
# These listeners represent the ports the
# services will listen on.
#

[Read-Only Listener]
type=listener
service=Read-Only Service
protocol=MySQLClient
port=4008

[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006

[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=default

保存并退出。
8.下面创建启动脚本

[root@localhost ~]# cp /usr/local/maxscale-2.1.3.centos.7/share/maxscale.service /usr/lib/systemd/system/
[root@localhost ~]# vim /usr/lib/systemd/system/maxscale.service

9.修改maxscale.service中的ExecStart=///bin/maxscale为ExecStart=/usr/local/maxscale/bin/maxscale

[root@localhost ~]# chmod 755 /usr/lib/systemd/system/maxscale.service
[root@localhost ~]# systemctl enable maxscale
[root@localhost ~]# systemctl daemon-reload
[root@localhost ~]# systemctl start maxscale

10.添加变量值

[root@localhost ~]# vi /etc/profile //最后一行添加以下内容保存退出!

PATH=$PATH:/usr/local/maxscale/bin
export PATH

[root@localhost ~]# source /etc/profile //使其变量立即生效

11.接下来就可以使用MaxAdmin进行管理。MaxAdmin是一个简单的客户端管理界面,可用于与MariaDB MaxScale服务器进行交互,可以显示MariaDB MaxScale内部的统计信息状态以及对MariaDB MaxScale操作的控制。详情:
//mariadb.com/kb/en/mariadb-enterprise/maxadmin-admin-interface/

[root@localhost ~]# maxadmin //回车
MaxScale> list servers
Servers.
---------------+--------------+-------+-------------+-----------------
Server | Address | Port | Connections | Status 
---------------+--------------+-------+-------------+-----------------
server1 | 172.16.8.56 | 3306 | 0 | Master, Running
server2 | 172.16.8.57 | 3306 | 0 | Slave, Running
server2 | 172.16.8.58 | 3306 | 0 | Slave, Running
---------------+--------------+-------+-------------+-----------------

12.至此MaxScale已经配置完成。现在就可以使用客户端连接Maxscale服务器端 端口为4006。

Windows下MySQL(MariaDB)数据库破解各版本root密码

方法一:(适用于MySQL5.0/5.1/5.2/5.5)

开始-运行-cmd 输入以下命令停止mysql,或者 开始-运行-services.msc 找到mysql服务并停止。

D:\MySQL\MySQL Server 5.2\bin>Net stop mysql

到mysql的具体安装路径BIN目录下执行:

D:\MySQL\MySQL Server 5.2\bin>mysqld-nt --skip-grant-tables
或
D:\MySQL\MySQL Server 5.2\bin>mysqld --skip-grant-tables

当前窗口将会停止。

重开cmd命令窗口到mysql的bin目录直接运行mysql

D:\MySQL\MySQL Server 5.2\bin>mysql
D:\MySQL\MySQL Server 5.2\bin>use mysql;
D:\MySQL\MySQL Server 5.2\bin>update user set password=password("RenwoleNEWPass") where user="root";
D:\MySQL\MySQL Server 5.2\bin>flush privileges;
D:\MySQL\MySQL Server 5.2\bin>exit;

方案二;(适用于mysql5.6/5.7/8.0)
以下操作到mysql安装目录BIN下的执行;

D:\MySQL\MySQL Server 5.6\bin>Net stop mysql
D:\MySQL\MySQL Server 5.6\bin>mysqld --skip-grant-tables
D:\MySQL\MySQL Server 5.6\bin>use mysql;
D:\MySQL\MySQL Server 5.6\bin>update mysql.user set authentication_string=password('RenwoleNEWPass') where user='root';
D:\MySQL\MySQL Server 5.6\bin>flush privileges;
D:\MySQL\MySQL Server 5.6\bin>exit;

最后结束mysql进程,重启mysql服务即可。

Linux Centos7 MariaDB(MySQL)10.2 主从半同步复制模式安装配置

实验环境情况;

操作系统:CentOS Linux release 7.3.1611 (Core)
数据库:MariaDB-10.2.6-linux-glibc_214-x86_64

1.实现半同步复制功能需要在主从服务器上安装google提供的插件。插件在MySQL二进制/usr/local/mysql/lib/plugin目录下,分别是;

主插件: semisync_master.so
从插件: sosemisync_slave.so

2.分别在主从节点上安装插件

3.主服务器安装插件:

MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; //安装插件 
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled = 1; //启动插件模块 
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_timeout = 1000; //设置超时时间

4.从服务器安装插件;

MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; //安装插件 
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled = 1; //启动插件模块
MariaDB [(none)]> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD; //重启进程使插件模块生效

5.在主Master和从Slave的my.cnf中编辑添加以下:

[mysqld]
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 1000

[mysqld]
rpl_semi_sync_slave_enabled = 1

6.我们至此已经实现了mysql数据库复制的半同步方式的架构。在主服务器的数据库中创建一个数据库或表,在从服务器停止后,查看主服务器是否初次延迟,二次创建数据库则降为异步同步,当从服务器启动后如果同步成功,那说明配置已经没有问题。

Linux MariaDB(MySQL)10.2 数据库基于GTID主从同步复制安装配置

原创作品,转载请注明本文地址

操作系统:CentOS Linux release 7.3.1611 (Core)
数据库:MariaDB-10.2.6-linux-glibc_214-x86_64

主服务器:10.10.10.56
从服务器:10.10.10.163

1.今天要做的事,单向主从同步

关于MariaDB(MySQL)数据库安装请参阅《Centos7.3 x64位二进制安装(MySQL)MariaDB 10.1.20数据库之生产篇》下面就开始MariaDB(MySQL)服务器集群的配置过程。

2.在MariaDB(MySQL)配置文件中修改或添加以下信息

vim /etc/my.cnf

主从通用配置

binlog-format = mixed #二进制日志记录的模式(高版本默认开启)
binlog-checksum = CRC32 #可使主机为写入二进制日志的事件写入校验(高版本默认开启)
sync-master-info = 1 #MariaDB依靠操作系统将master.info文件刷新到磁盘。
sync_relay_log_info = 1 #MariaDB依靠操作系统将relay-log.info文件刷新到磁盘。
expire_logs_days = 7 #日志文件过期天数,默认是 0,表示不过期 
master-verify-checksum = 1 #主服务器效验
slave-sql-verify-checksum = 1 #从服务器效验

3.主服务器Master除了通用配置外,还需要加入以下代码

server-id = 56 #MySQL服务器ID,不重复
log-bin = mysql-bin #二进制日志(默认开启)
sync-binlog = 1 #主服务器进行设置,用于事务安全
log-bin-index = mysql-bin

4.从服务器Slave除了通用配置外,还需要加入以下代码

server-id = 163
relay-log = relay-bin #中继日志
slave-parallel-threads = 2 #设定从服务器的SQL线程数
#replicate-do-db = renwoleblogdb#复制指定的数据库,多个写多行
replicate-ignore-db = mysql #不备份的数据库,多个写多行
relay_log_recovery = 1 #从站崩溃后可以使用,防止损坏的中继日志处理。
log-slave-updates = 1 #slave将复制事件写进自己的二进制日志
relay-log-index = relay-bin

此外Mysql从服务器没有必要开启二进制日志,但是在一些情况下,必须设置,例如;如果slave为其它slave的master,必须设置bin_log。我这里就默认开启。

5.以上只是简单的介绍了每个参数的作用,这些参数的具体设置还需根据用户的实际情况进行相关调整,具体可到官方了解

《复制和二进制日志服务器系统变量》
//mariadb.com/kb/en/mariadb/replication-and-binary-log-server-system-variables/

关于系统变量的兼容性,可参阅官方

《MariaDB与MySQL兼容性》
//mariadb.com/kb/en/mariadb/mariadb-vs-mysql-compatibility/

6.主服务器Master授权配置

主MariaDB服务器上创建专用账号并授权数据库权限,以及从服务器IP的远程访问

# mysql -uroot -p
Enter password:【输入你的MySQL密码回车】
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'renwoleuseracc'@'%' IDENTIFIED BY 'renwoleuserpass'; //创建Slave专用备份账号
MariaDB [(none)]> flush privileges; //刷新MySQL权限
MariaDB [(none)]> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user; //查看授权情况
MariaDB [(none)]> flush tables with read lock; //锁定数据库防止master值变化
MariaDB [(none)]> show master status; //获取master状态值
+-----------------+----------+------------+-----------------+
| File |Position |Binlog_Do_DB|Binlog_Ignore_DB |
+-----------------+----------+------------+-----------------+
| mysql-bin.000006| 627 | | |
+-----------------+----------+------------+-----------------+
1 row in set (0.00 sec)

7.一旦获取了备份时正确的Binlog位点(文件名和偏移量),那么就可以用BINLOG_GTID_POS()函数来计算GTID

MariaDB [(none)]> SELECT BINLOG_GTID_POS("mysql-bin.000006", 627);
+------------------------------------------+
| BINLOG_GTID_POS('mysql-bin.000006', 627) |
+------------------------------------------+
| 0-56-4 |
+------------------------------------------+
1 row in set (0.01 sec)

8.从服务器Slave配置

正如官方所说从MariaDB 10.0.13版本开始,mysqldump会自动完成这个工作,并且把GTID的写在导出文件中,只要设置 –master-data 或 –dump-slave 的同时设置 –gtid 即可。

这样的话新的SLAVE就可以通过设置 @@gtid_slave_pos 的值来设定复制的起始位置,用 CHANGE MASTER 把这个值传给主库,然后开始复制:

# mysql -uroot -p
Enter password:【输入你的MySQL密码】
MariaDB [(none)]> SET GLOBAL gtid_slave_pos = "0-56-4";
MariaDB [(none)]> change master to master_host='10.10.10.56',MASTER_PORT = 3306,master_user='renwoleuseracc',master_password='renwoleuserpass',master_use_gtid=slave_pos; //进行主从授权
MariaDB [(none)]> START SLAVE; //启动Slave
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 10.10.10.56
 Master_User: renwoleuseracc
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000006
 Read_Master_Log_Pos: 627
 Relay_Log_File: relay.000035
 Relay_Log_Pos: 537
 Relay_Master_Log_File: mysql-bin.000006
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 ...
 ...
 ...
 Using_Gtid: Slave_pos
 Gtid_IO_Pos: 0-56-4

9.如果 Slave_IO_Running 与 Slave_SQL_Running 都为YES,表明从服务已经运行,Using_Gtid列判断GTID值是否一致。
说明:

master_host 表示master授权地址
MASTER_PORT MySQL端口
master_user 表示master授权账号
master_password 表示密码
master_use_gtid GTID变量值

10.接下来解锁主服务器数据库表

MariaDB [(none)]> unlock tables; //解锁数据表
MariaDB [(none)]> show slave hosts; //查看从服务器连接状态
MariaDB [(none)]> show global status like "rpl%"; //查看客户端

11.从服务器Slave查看relay的所有相关参数

MariaDB [(none)]> show variables like '%relay%';

12.主从已经配置完成。现在无论在主服务器上增、改、删、查,都会同步到从服务器,根据自己的需求进行相关测试即可。
关于master slave重置语法

重置master的核心语法

RESET MASTER; 意思是执行 RESET MASTER 将删除所有二进制日志文件,并创建一个空白的二进制日志文件,数字后缀为.000001,RESET MASTER 不会影响SLAVE服务器上的工作状态,所以执行这个命令会导致Slave找不到Master的binlog,从而造成同步失败。

重置slave的核心语法

RESET SLAVE; 含义是;RESET SLAVE 将清除slave上的同步位置并删除所有旧的同步中继日志文件,但重置前必须先停止slave服务(STOP SLAVE)

以后有时间,我将介绍基于GTID的半同步主从的文章,这也是在生产中所需要记录下来,以备以后使用。

更多介绍://mysql.taobao.org/monthly/2016/02/08/

linux MariaDB(MySQL)数据库更改用户权限

平时维护MariaDB(MySQL)数据库服务器,难免会用到一些常用的命令,MariaDB数据库长时间不出问题,有些sql语句就会忘记,之前也没有记载,今天没事就记录下,也共享给大家一块看看,有不足之处还望谅解。

本文操作适用于MariaDB所有版本,适用于MySQL5.2以上版本
本文生产环境Centos7.3 64位 ,MariaDB server 10.2.5

MariaDB 赋予用户权限命令的简单格式可概括为:

grant 权限 on 数据库对象 to 用户;

# mysql -u root -p //登录数据库
 Enter password:
 MariaDB [(none)]> show databases; //查看当前数据库中所有数据库
 MariaDB [(none)]> create database renwole; //新建数据库名为“renwole”
 MariaDB [renwoleBD]> show tables; //显示某个数据库中的表文件
 MariaDB [(none)]> select version(),current_date; //查看数据库版本和当前日期
 MariaDB [(none)]> drop database renwole; //删除renwole数据库
 MariaDB [renwoleDB]> desc 表名称; //查看数据库表结构
 MariaDB [(none)]> show variables like '%dir%'; //查看数据库存储路径
 MariaDB [(none)]> show grants; 查看当前用户权限
 MariaDB [(none)]> show grants for root@'localhost'; //查看用户权限

下面就MariaDB数据库实例讲解,例如:

创建添加一个renwole用户,密码为renwole123

# mysql -u root -p //登录数据库
 Enter password:
 MariaDB [(none)]> insert into mysql.user(Host,User,Password) values("localhost","renwole",password("renwole123"));

新建一个renwoleDB数据库,并授权用户renwole拥有该数据库的所有权限。

MariaDB [(none)]> create database renwoleDB; //新建
 MariaDB [(none)]> grant all privileges on renwoleDB.* to renwole@% identified by 'renwole123'; //授权,关键字 “privileges” 可以省略。
 MariaDB [(none)]> flush privileges; //刷新用户权限

如果想给一个用户查询、插入、更新、删除数据库中的所有表数据权利,可以这样来写:

MariaDB [(none)]> grant select on on renwoleDB.* to renwole@’%’ //查询
 MariaDB [(none)]> grant insert on on renwoleDB.* to renwole@’%’ //插入
 MariaDB [(none)]> grant update on on renwoleDB.* to renwole@’%’ //更新
 MariaDB [(none)]> grant delete on on renwoleDB.* to renwole@’%’ //删除

或者,用一个语句命令替代:

MariaDB [(none)]> grant select,insert,delete,update on renwoleDB.* to renwole@’%’ identified by 'renwole123';

如果想查看所有Mysql用户的权限,代码如下;

MariaDB [(none)]> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

如果想删除一个用户及权限,可以这样写;

MariaDB [(none)]> drop user renwole@localhost;

如果想修改一个用户密码;

MariaDB [(none)]> update mysql.user set password=password('New-password') where User="renwole" and Host="%";
 MariaDB [(none)]> flush privileges;

添加高级root用户整个mysql服务器权限

grant all on *.* to root@'%' identified by 'Password';

注意:【identified by】这个句子可以顺带设置密码,如果不指定该用户口令不变。

撤销已经赋予给MariaDB用户的权限。
revoke 跟 grant 的语法相似,只需要把关键字 “to” 换成 “from” 即可,例如:

grant all on *.* to renwole@%;
 revoke all on *.* from renwole@%;

MariaDB数据库的 grant、revoke 用户权限注意事项;grant, revoke 用户权限后,该用户只有重新连接 MySQL 数据库,权限才能生效。如果想让授权的用户,也可以将这些权限 grant 给其他用户,需要选项 “grant option“,例如:

MariaDB [(none)]> grant select on renwoleDB.* to renwole@% with grant option;

这个特性一般用不到。实际中,数据库权限最好由root用户来统一管理。

注意:有时候renwole@’%’授权任意主机连接的时候需要加单引号,但有时又不需要。