Percona XtraDB Cluster添加仲裁节点

Galera Arbitrator是Percona XtraDB集群的成员,用于投票,以防您拥有少量服务器(通常为两个)并且不希望添加更多资源。Galera仲裁器不需要专用服务器。它可以安装在运行其他应用程序的机器上。只要确保它具有良好的网络连接。Galera Arbitrator是参与投票的群集的成员,但不是实际复制(虽然它接收与其他节点相同的数据)。此外,它不包含在流量控制计算中。本文给出添加仲裁节点示例。

一、PXC集群环境描述
192.168.1.248 CentOS7.4
192.168.1.249 CentOS7.4
192.168.1.253 CentOS6.7(新增用于作为仲裁节点)

如下图,将仲裁节点添加到现有集群

二、添加仲裁节点
# yum install Percona-XtraDB-Cluster-garbd-57

# rpm -ql Percona-XtraDB-Cluster-garbd-57
/etc/init.d/garb ##启动脚本
/etc/sysconfig/garb ##配置文件
/usr/bin/garbd
/usr/share/doc/percona-xtradb-cluster-garbd-3/COPYING
/usr/share/doc/percona-xtradb-cluster-garbd-3/README
/usr/share/man/man8/garbd.8.gz
/var/lib/galera

# vim /etc/sysconfig/garb

GALERA_NODES=”192.168.1.248:4567, 192.168.1.249:4567, 192.168.1.253:4567″
GALERA_GROUP=pxc-cluster

##配置后请删除无关的全部内容,否则报如下错误
Garbd config /etc/sysconfig/garb is not configured yet [失败]

# /etc/init.d/garb start
正在启动 /usr/bin/garbd: [确定]

# /etc/init.d/garb start
正在启动 /usr/bin/garbd: [确定]
#
# /etc/init.d/garb status
garbd (pid 5198) 正在运行…
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
三、验证集群
# tail -fn 50 /var/log/message
Apr 13 09:13:22 ydq4 garbd[7854]: CRC-32C: using hardware acceleration.
Apr 13 09:13:22 ydq4 garbd[7854]: Read config: #012#011daemon: 1#012#011name:
garb#012#011address: gcomm://192.168.1.248:4567,192.168.1.249:4567,192.168.1.253:4567#012#011group:
pxc-cluster#012#011sst: trivial#012#011donor: #012#011options: gcs.fc_limit=9999999;
gcs.fc_factor=1.0; gcs.fc_master_slave=yes#012#011cfg: #012#011log:
Apr 13 09:13:22 ydq4 garbd[7856]: Using CRC-32C for message checksums.
Apr 13 09:13:22 ydq4 garbd[7856]: gcomm thread scheduling priority set to other:0
Apr 13 09:13:22 ydq4 garbd[7856]: Fail to access the file (./gvwstate.dat) error (No such file or directory).
It is possible if node is booting for first time or re-booting after a graceful shutdown
Apr 13 09:13:22 ydq4 garbd[7856]: Restoring primary-component from disk failed. Either node is booting for
first time or re-booting after a graceful shutdown
Apr 13 09:13:22 ydq4 garbd[7856]: GMCast version 0
Apr 13 09:13:22 ydq4 garbd[7856]: (dc339071, ‘tcp://0.0.0.0:4567’) listening at tcp://0.0.0.0:4567
Apr 13 09:13:22 ydq4 garbd[7856]: (dc339071, ‘tcp://0.0.0.0:4567’) multicast: , ttl: 1
Apr 13 09:13:22 ydq4 garbd[7856]: EVS version 0
Apr 13 09:13:22 ydq4 garbd[7856]: gcomm: connecting to group ‘pxc-cluster’, peer ‘192.168.1.248:4567,192.168.1.249:4567,192.168.1.253:4567’
Apr 13 09:13:22 ydq4 garbd[7856]: (dc339071, ‘tcp://0.0.0.0:4567’) connection established to dc339071 tcp://192.168.1.253:4567
Apr 13 09:13:22 ydq4 garbd[7856]: (dc339071, ‘tcp://0.0.0.0:4567’) address ‘tcp://192.168.1.253:4567’ points to own listening address, blacklisting
Apr 13 09:13:22 ydq4 garbd[7856]: (dc339071, ‘tcp://0.0.0.0:4567’) connection established to 66d23117 tcp://192.168.1.249:4567
Apr 13 09:13:22 ydq4 garbd[7856]: (dc339071, ‘tcp://0.0.0.0:4567’) connection established to a643db62 tcp://192.168.1.248:4567
Apr 13 09:13:22 ydq4 garbd[7856]: (dc339071, ‘tcp://0.0.0.0:4567’) turning message relay requesting on, nonlive peers:
Apr 13 09:13:23 ydq4 garbd[7856]: declaring 66d23117 at tcp://192.168.1.249:4567 stable
Apr 13 09:13:23 ydq4 garbd[7856]: declaring a643db62 at tcp://192.168.1.248:4567 stable
Apr 13 09:13:23 ydq4 garbd[7856]: Node 66d23117 state primary
Apr 13 09:13:23 ydq4 garbd[7856]: Current view of cluster as seen by this node#012view (view_id(PRIM,66d23117,16)#012memb
{#012#01166d23117,0#012#011a643db62,0#012#011dc339071,0#012#011}#012joined {#012#011}#012left {#012#011}#012partitioned {#012#011}#012)
Apr 13 09:13:23 ydq4 garbd[7856]: Save the discovered primary-component to disk
Apr 13 09:13:23 ydq4 garbd[7856]: open file(./gvwstate.dat.tmp) failed(Permission denied)
Apr 13 09:13:23 ydq4 garbd[7856]: gcomm: connected
Apr 13 09:13:23 ydq4 garbd[7856]: Shifting CLOSED -> OPEN (TO: 0)
Apr 13 09:13:23 ydq4 garbd[7856]: New COMPONENT: primary = yes, bootstrap = no, my_idx = 2, memb_num = 3
Apr 13 09:13:23 ydq4 garbd[7856]: STATE EXCHANGE: Waiting for state UUID.
Apr 13 09:13:23 ydq4 garbd[7856]: STATE EXCHANGE: sent state msg: dc5dbeeb-3eb7-11e8-8e45-e34b2f81e1c9
Apr 13 09:13:23 ydq4 garbd[7856]: STATE EXCHANGE: got state msg: dc5dbeeb-3eb7-11e8-8e45-e34b2f81e1c9 from 0 (pxc-cluster-node-2)
Apr 13 09:13:23 ydq4 garbd[7856]: STATE EXCHANGE: got state msg: dc5dbeeb-3eb7-11e8-8e45-e34b2f81e1c9 from 1 (pxc-cluster-node-1)
Apr 13 09:13:23 ydq4 garbd[7856]: STATE EXCHANGE: got state msg: dc5dbeeb-3eb7-11e8-8e45-e34b2f81e1c9 from 2 (garb)
Apr 13 09:13:23 ydq4 garbd[7856]: Quorum results:#012#011version = 4,#012#011component = PRIMARY,#012#011conf_id = 4,
#012#011members = 2/3 (primary/total),#012#011act_id = 248529,#012#011last_appl. = -1,
#012#011protocols = 0/7/3 (gcs/repl/appl),#012#011group UUID = cd96b06a-0a1d-11e8-99d2-837e6f3b95a9
Apr 13 09:13:23 ydq4 garbd[7856]: Flow-control interval: [8388607, 8388607]
Apr 13 09:13:23 ydq4 garbd[7856]: Trying to continue unpaused monitor
Apr 13 09:13:23 ydq4 garbd[7856]: Shifting OPEN -> PRIMARY (TO: 248529)
Apr 13 09:13:23 ydq4 garbd[7856]: Sending state transfer request: ‘trivial’, size: 7
Apr 13 09:13:23 ydq4 garbd[7856]: Member 2.0 (garb) requested state transfer from ‘*any*’. Selected 0.0 (pxc-cluster-node-2)(SYNCED) as donor.
Apr 13 09:13:23 ydq4 garbd[7856]: Shifting PRIMARY -> JOINER (TO: 248529)
Apr 13 09:13:23 ydq4 garbd[7856]: 0.0 (pxc-cluster-node-2): State transfer to 2.0 (garb) complete.
Apr 13 09:13:23 ydq4 garbd[7856]: 2.0 (garb): State transfer from 0.0 (pxc-cluster-node-2) complete.
Apr 13 09:13:23 ydq4 garbd[7856]: SST leaving flow control
Apr 13 09:13:23 ydq4 garbd[7856]: Shifting JOINER -> JOINED (TO: 248529)
Apr 13 09:13:23 ydq4 garbd[7856]: Member 0.0 (pxc-cluster-node-2) synced with group.
Apr 13 09:13:23 ydq4 garbd[7856]: Member 2.0 (garb) synced with group.
Apr 13 09:13:23 ydq4 garbd[7856]: Shifting JOINED -> SYNCED (TO: 248529) –>这里提示状态由加入变为同步
Apr 13 09:13:26 ydq4 garbd[7856]: (dc339071, ‘tcp://0.0.0.0:4567’) turning message relay requesting off
### Author : Leshami
### Blog : http://blog.csdn.net/leshami
注意,其余的2个节点的wsrep_cluster_address参数要将仲裁节点地址添加进去
##在集群的节点查看,此时wsrep_cluster_size已经变成3了
mysql> show global status like ‘%wsrep_cluster%’;
+————————–+————————————–+
| Variable_name | Value |
+————————–+————————————–+
| wsrep_cluster_conf_id | 5 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | cd96b06a-0a1d-11e8-99d2-837e6f3b95a9 |
| wsrep_cluster_status | Primary |
+————————–+————————————–+

— pxc版本
mysql> show variables like ‘version%’;
+————————-+—————————————————————–+
| Variable_name | Value |
+————————-+—————————————————————–+
| version | 5.7.20-18-57-log |
| version_comment | Percona XtraDB Cluster (GPL), WSREP version 29.24, wsrep_29.24 |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+————————-+—————————————————————–+
———————

企业主流MySQL高可用集群架构之PXC及常见问题解决

企业主流MySQL高可用集群架构之PXC

及Starting MySQL (Percona XtraDB Cluster)State transfer in progress, setting sleep higher
. ERROR! The server quit without updating PID file (/data/mysql/mysql.pid).问题解决。

https://github.com/walt008/AutoPXC

PXC是percona公司的percona xtraDB cluster.简称PXC。它是基于GaLera协议的高可用集群方案。可以实现多个节点间的数据同步复制以及读写,并且可保障数据库的服务高可用及数据强一致性

PXC 架构图:

 

 


三个节点都已经启动成功,这样在任意一个节点上面,执行一条DML语句的操作,都会同步到另外两个节点。

 

 

 

pxc就属于一套近乎完美的MySQL高可用集群架构方案;

优点总结:

  1. 可以达到时时同步,无延迟现象发生
  2. 完全兼容MySQL
  3. 对于集群中新节点的加入,维护起来很简单
  4. 数据的强一致性

不足之处总结:

  1. 只支持Innodb存储引擎
  2. 存在多节点update更新问题,也就是写放大问题
  3. 在线DDL语句,锁表问题
  4. sst针对新节点加入的传输代价过高的问题

实战过程:

环境介绍:系统是centos 7.4 1708 版本,理论上linux7以上适用

12

3

192.168.150.138  walt001192.168.150.139  walt002

192.168.150.143  walt003

 

安装之前的注意事项:

三台机器的防火墙iptables都要关闭,三台机器的server-id不能一样。

PXC软件包下载:

xtrabackup下载地址:

https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.6/binary/tarball/percona-xtrabackup-2.4.6-Linux-x86_64.tar.gz

Percona-XtraDB-Cluster 下载:

https://www.percona.com/downloads/Percona-XtraDB-Cluster-56/Percona-XtraDB-Cluster-5.6.26-25.12/binary/tarball/Percona-XtraDB-Cluster-5.6.26-rel74.0-25.12.1.Linux.x86_64.tar.gz

 

这里我下载的是:Percona-XtraDB-Cluster-5.6.26-rel74.0-25.12.1.Linux.x86_64

接下来三台机器上都需要针对基础软件包进行安装,使用yum安装即可,解决依赖性。

三台机器都需要操作的步骤:

  • 把IP和主机名写到hosts文件里(这个不是主要项,方便主机名解析而已,我没有完全一样的hosts结果也成功,不成功 不用在这里纠结)

[root@xxx]# cat /etc/hosts

192.168.150.138  walt001

192.168.150.139  walt002

192.168.150.143  walt003

 因为pxc本身包含mysql主程序,为防止端口占用删除多余数据库安装包

yum remove mariadb-* -y

安装依赖

yum install perl-IO-Socket-SSL.noarch perl-DBD-MySQL.x86_64 perl-Time-HiRes openssl openssl-devel socat -y

提前告诉你,如果系统是最小化安装事先做这两个软连接避免初始化报库文件缺失错误。

[root@xxx ~]# ln -sv /usr/lib64/libcrypto.so.10 /lib64/libcrypto.so.6

[root@xxx ~]# ln -sv /usr/lib64/libssl.so.10 /lib64/libssl.so.6

 

定位到解压目录 

[root@xxx ~]# cd /usr/local/

[root@xxx local]# tar xvf /rrot/percona-xtrabackup-2.4.6-Linux-x86_64.tar.gz 

[root@xxx local]# tar xv/root/Percona-XtraDB-Cluster-5.6.26-rel74.0-25.12.1.Linux.x86_64.tar.gz

建个软连接,方便使用

[root@xxx local]# ln -sv Percona-XtraDB-Cluster-5.6.26-rel74.0-25.12.1.Linux.x86_64 mysql

[root@xxx local]# useradd  -s /sbin/nologin mysql

[root@xxx local]# chown mysql.mysql -R mysql

[root@xxx local]# cp percona-xtrabackup-2.4.6-Linux-x86_64/bin/* mysql/bin/

[root@xxx local]# mkdir -p /data/mysql

[root@xxx local]# chown mysql.mysql -R /data/mysql/

先配置第一台机器,并成功启动后再配置其他两台

在第一台主机上创建配置文件:

创建配置文件:

[root@xxx local]# vim /etc/my.cnf

[mysqld]

datadir=/data/mysql

socket = /tmp/mysql.sock

pid-file=/data/mysql/mysql.pid

character_set_server = utf8

max_connections = 3000

back_log= 3000

skip-name-resolve

sync_binlog=0

innodb_flush_log_at_trx_commit=1

server-id = 1

#pxc主要配置项

default_storage_engine=Innodb

innodb_autoinc_lock_mode=2

binlog_format=row

wsrep_cluster_name=pxc_zs  #集群名称

wsrep_slave_threads=4   #开启的复制线程数,cpu核数*2

wsrep_cluster_address=gcomm://192.168.150.138,192.168.150.39,192.168.150.143 #集群所有服务器ip都加进来

wsrep_node_address=192.168.150.138 #本机ip

wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so

wsrep_sst_method=xtrabackup-v2 

wsrep_sst_auth=sst:zs

 把这个文件写好了,传到其他两台机器的 /etc 下面,修改 server-id 和 wsrep_node_address

每个主机的server-id 和wsrep_node_address 保持不同即可

初始化数据:

/usr/local/mysql/scripts/mysql_install_db –basedir=/usr/local/mysql –datadir=/data/mysql/ –defaults-file=/etc/my.cnf –user=mysql

复制进程文件到系统服务目录,启动第一节点的服务:

[root@xxx local]#cd /usr/local/mysql/support-files

[root@xxx support-files]#cp mysql.server /etc/init.d/mysql

[root@xxx support-files]# /etc/init.d/mysql bootstrap-pxc

创建环境变量,让系统变量读到MySQL的路径:

[root@xxx opt]# echo “export PATH=$PATH:/usr/local/mysql/bin” > /etc/profile.d/mysql.sh && source /etc/profile.d/mysql.sh

登陆或者初始化的时候提示缺少库文件,find命令依次查找库文件做软连接到/lib64

[root@xxx ~]# find / -name “libreadline*”

/usr/lib64/libreadline.so.6

/usr/lib64/libreadline.so.6.2

[root@xxx ~]# ln -sv /usr/lib64/libreadline.so.6 /lib64/libreadline.so.5

[root@xxx ~]# ln -sv /usr/lib64/libcrypto.so.10 /lib64/libcrypto.so.6

[root@xxx ~]# ln -sv /usr/lib64/libssl.so.10 /lib64/libssl.so.6

 

启动成功之后,维护数据库环境并创建用户

进入数据库 ,默认密码是空的

#mysql -uroot -p

mysql> delete from mysql.user where user!=’root’ or host!=’localhost’;

mysql> grant all privileges on *.* to ‘sst‘@’%’ identified by ‘zs’;

mysql> grant all privileges on *.* to ‘sst’@’localhost’ identified by ‘zs’;

mysql> flush privileges;     #mysql 5.6以后这个命令可以不用,授权都是立即生效的

当第一个主节点成功启动,并创建了授权的用户后,开始配置其他的机器

安装步骤和上面相同,只要保证配置文件的server-id 和wsrep_node_address 不同即可

wsrep_node_address 填写当前机器的IP

启动命令和第一台不同,用下面的命令启动:

[root@xxx mysql]# /etc/init.d/mysql start

第二台动后查看集群状态:

在第一个机器上执行:

[root@xxx mysql]#mysql -v  #空密码的状态下这个命令可以直接登录mysql

mysql> show status like ‘wsrep%’;

注意:我一开始第二台怎么都启动不了,  刷日志发现一直连接超时,各种抓狂,然后试着远程连接mysql   失败,想到应该是被防火墙挡住了,出现开头那个报错。

#关闭selinux,防火墙打开pxc所需端口,或者取消注释直接关闭防火墙
sed -i s/”SELINUX=enforcing”/”SELINUX=disabled”/g  /etc/selinux/config #reboot后生效
setenforce 0  #临时关闭selinux
#systemctl stop firewalld
#systemctl disable firewalld
#iptables -F
firewall-cmd –zone=public –add-port=80/tcp –permanent
firewall-cmd –zone=public –add-port=3306/tcp –permanent
firewall-cmd –zone=public –add-port=4567/tcp –permanent
firewall-cmd –zone=public –add-port=4568/tcp –permanent
firewall-cmd –zone=public –add-port=4444/tcp –permanent
firewall-cmd –reload

生产环境还是添加端口为好,有硬件防火墙,直接关闭系统防火墙即可。

后面的第三台,第xx台就没什么问题了。集群创建成功后,执行 mysql_secure_installation 来安全初始化。

安全初始化如果报错(概率不大)可以尝试登录mysql执行:

set global wsrep_provider_options=”pc.bootstrap  = yes”;

当然在生产环境,我还是小心的配置了ip访问限制,安全才是王道。

注意:

全部关闭需要采用滚动关闭方式:

1.  关闭node1,修复完后,启动加回来;

2.  关闭node2,    修复完后,启动加回来;

3. ………………….,直到最后一个节点

4.  原则要保持Group里最少一个成员活着

数据库关闭之后,最会保存一个last Txid,所以启动时,先要启动最后一个关闭的节点,启动顺序和关闭顺序刚好相反。

wsrep_recover=on参数在启动时加入,用于从log中分析gtid。

怎样避免关闭和启动时数据丢失?

1. 所有的节点中最少有一个在线,进行滚动重启;

2. 利用主从的概念,把一个从节点转化成PXC里的节点。

pxc注意的问题

1. 脑裂

任何命令执行出现unkown command ,表示出现脑裂,集群两节点间4567端口连不通,无法提供对外服务。

SET GLOBAL wsrep_provider_options=”pc.ignore_sb=true”;

2. 并发写

三个节点的自增起始值为1、2、3,步长都为3,解决了insert问题,但update同时对一行操作就会有问题,出现:Error: 1213 SQLSTATE: 40001,所以更新和写入在一个节点上操作。

3. DDL

引起全局锁,采用:pt-online-schema-change

4. MyISAM引擎不能被复制,只支持innodb

5. pxc结构里面必须有主键

如果没有主建,有可能会造成集中每个节点的Data page里的数据不一样

6. 不支持表级锁

不支持lock /unlock tables

7. pxc里只能把slow log ,query log 放到File里
8. 不支持XA事务
9. 性能由集群中性能最差的节点决定

如果不幸PXC全部节点宕机,无法连接,那么先启动第一节点,后面的节点cd到mysql,data目录下:

如:

#cd /data.mysql

#rm -f gvwstate.dat

这样后面的节点就可以启动了。

mysql 启动报错 os_file_get_status() failed on ‘./ibdata1’. Can’t determine file permissions

2019-03-23T14:11:41.314049Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
2019-03-23T14:11:41.653637Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-03-23T14:11:41.751144Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-03-23T14:11:41.810000Z 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: 950424ef-4d75-11e9-bdba-6c2b598040ce.
2019-03-23T14:11:41.811805Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed’ cannot be opened.
2019-03-23T14:11:41.812121Z 1 [Note] A temporary password is generated for root@localhost: hBpK8qHJLJ.A
2019-03-23T14:11:50.198780Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
2019-03-23T14:11:50.198872Z 0 [Warning] Can’t create test file /data1/mysql/store.lower-test
2019-03-23T14:11:50.198896Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.21) starting as process 8183 …
2019-03-23T14:11:50.200802Z 0 [Warning] Can’t create test file /data/mysql/store.lower-test
2019-03-23T14:11:50.200817Z 0 [Warning] Can’t create test file /data/mysql/store.lower-test
2019-03-23T14:11:50.201750Z 0 [Note] InnoDB: PUNCH HOLE support available
2019-03-23T14:11:50.201760Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-03-23T14:11:50.201767Z 0 [Note] InnoDB: Uses event mutexes
2019-03-23T14:11:50.201770Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2019-03-23T14:11:50.201773Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2019-03-23T14:11:50.201775Z 0 [Note] InnoDB: Using Linux native AIO
2019-03-23T14:11:50.201902Z 0 [Note] InnoDB: Number of pools: 1
2019-03-23T14:11:50.201953Z 0 [Note] InnoDB: Using CPU crc32 instructions
2019-03-23T14:11:50.202683Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2019-03-23T14:11:50.207014Z 0 [Note] InnoDB: Completed initialization of buffer pool
2019-03-23T14:11:50.208116Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2019-03-23T14:11:50.218173Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2019-03-23T14:11:50.218181Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
2019-03-23T14:11:50.218184Z 0 [ERROR] InnoDB: os_file_get_status() failed on ‘./ibdata1’. Can’t determine file permissions
2019-03-23T14:11:50.218187Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2019-03-23T14:11:50.818568Z 0 [ERROR] Plugin ‘InnoDB’ init function returned error.
2019-03-23T14:11:50.818573Z 0 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
2019-03-23T14:11:50.818576Z 0 [ERROR] Failed to initialize builtin plugins.
2019-03-23T14:11:50.818577Z 0 [ERROR] Aborting

解决办法:

  1. 查看配置文件里面指定的目录是否有权限,所属用户和组是否正确:socket=/var/lib/mysql/mysql.sock
    character-set-server=utf8
    symbolic-links=0
    datadir=/data/mysql
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
  2. 关闭SELINUX:setenforce 0

参考链接:

https://blog.csdn.net/agonisq/article/details/79817397

mysql 5.7修改root密码 密码过期

mysql 5.7修改root密码 密码过期:

mysql> ALTER USER ‘m3’@’%’ PASSWORD EXPIRE INTERVAL 365 DAY;
Query OK, 0 rows affected (0.10 sec)

mysql> ALTER USER ‘m5’@’%’ PASSWORD EXPIRE INTERVAL 365 DAY;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER ‘sdfg’@’%’ PASSWORD EXPIRE INTERVAL 365 DAY;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER ‘uadmin’@’%’ PASSWORD EXPIRE INTERVAL 1000 DAY;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER ‘nossl’@’%’ PASSWORD EXPIRE INTERVAL 600 DAY;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER ‘mm’@’%’ PASSWORD EXPIRE INTERVAL 365 DAY;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER ‘mm_ps’@’%’ PASSWORD EXPIRE INTERVAL 365 DAY;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER ‘root’@’%’ PASSWORD EXPIRE INTERVAL 3650 DAY;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.07 sec)

密码过期

mysql 内执行 修改密码永不过期。

ALTER USER ‘root’@’localhost’ PASSWORD EXPIRE NEVER

或者

SET GLOBAL default_password_lifetime = 0;  — never 全局用户

或者

ALTER USER ‘root’@’localhost’ PASSWORD EXPIRE INTERVAL 90 DAY; — 90 days

 

附:

配置密码过期时间my.cnf:

expire_logs_days = 365
expire_logs_days = 200

mysql查询结果输出到文件

mysql查询结果输出到文件

方式一

在mysql命令行环境下执行: sql语句+INTO OUTFILE +文件路径/文件名 +编码方式(可选)

例如:

SELECT * FROM (表名) INTO OUTFILE '/usr/local/test.txt' ;

注意事项:

1)可能会报没有 select command denied(没有查询权限) 或者Access denied for user(没有file权限) ,增加权限之后即可。

2)不能存在同名文件,否则sql执行失败。

3)生成文件格式也可以是.txt/.xls/.csv。

4) 生成的文件中可能会有中文乱码问题,可以在语句后面+CHARACTER SET gbk (utf8等)

例如: SELECT * FROM (表名) INTO OUTFILE '/usr/local/test.txt' CHARACTER SET gbk;

5)如果sql查询出来的数据包含有很大的数值型数据,则在excel中这些数值数据可能会出问题,因此,可以先导出为.txt/.csv文件格式,再复制黏贴到excel文件中(首先设置单元格格式为文本)

方式二

在登录某服务器后,采用 mysql 命令执行 ,不需要登录进mysql命令行环境下。

例如:

mysql -h 127.0.0.1 -u用户名 -p密码 --default-character-set=gb2312 -e "select * from 表名" 数据库名 > test.txt

注:-e 查询条件语句 后面跟上目标数据库

若有中文乱码,添加设置编码方式 utf8 、gbk

 

 

不登陆数据库执行 MySQL 命令

有的时候需要查看数据库的某些信息,然后继续接下来的 shell 命令操作,登录数据库在退出嫌麻烦可以使用这招:

例 1:列出所有数据库

mysql -h host_name -P3306 -u user_name -p'password' -se "show databases;"

例 2:列出 database 下的所有表

mysql -h host_name -P3306 -u user_name -p'password' -D database -se "show tables;"

host_name: 数据库 host 或者 IP 地址;

user_name: 登录数据库用户名;

password:登录数据库密码;

database: 数据库名;

例:

[root@localhost tmp]# mysql -h 172.16.30.50 -uroot -proot -D plat -se "SELECT ip_str,major_version,minor_version,vendor,series,model FROM plat_resource_config"

 

mysqldump 警告:Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don’t want to restore GTIDs, pass –set-gtid-purged=OFF. To make a complete dump, pass –all-databases –triggers –routines –events.

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don’t want to restore GTIDs, pass –set-gtid-purged=OFF. To make a complete dump, pass –all-databases –triggers –routines –events.:

示例:

mysqldump -h 192.168.1.15 -P 3308 -utestuser -p test –set-gtid-purged=off > /tmp/test.sql

第一篇关于mysql的文章,留个纪念

1,使用mysqldump时报错(1064),这个是因为mysqldump版本太低与当前数据库版本不一致导致的。
mysqldump: Couldn’t execute ‘SET OPTION SQL_QUOTE_SHOW_CREATE=1’: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ‘OPTION SQL_QUOTE_SHOW_CREATE=1’ at line 1 (1064)

[mysql@cnsz081066 18:12:30 ~/xionglang]
$mysqldump –version
mysqldump  Ver 10.13 Distrib 5.1.61, for redhat-linux-gnu (x86_64)

$mysql -uroot –socket=/wls/mysql/run/mysql.sock –port=3322
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 47281134
Server version: 5.6.14-enterprise-commercial-advanced-log MySQL Enterprise Server – Advanced Edition (Commercial)

这样的话必须知道mysqldump的绝对路径,在mysql的安装目录下有。

2,导出时指定字符集,报错Character set ‘utf-8’ is not a compiled character set and is not specifie .
–default-character-set=utf-8

这个是因为字符集错了。是–default-character-set=utf8

3,导出时提示warning,A partial dump from a server that has GTIDs
$/wls/mysql/bin/mysqldump -uroot –socket=/wls/mysql/run/mysql.sock –default-character-set=utf8  –port=3309 hm_stg ofmucroom>exptab1_hm_stg_3309.sql
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that
changed suppressed parts of the database. If you don’t want to restore GTIDs, pass –set-gtid-purged=OFF. To make a complete
dump, pass –all-databases –triggers –routines –events.

关于GTID是5.6以后,加入了全局事务 ID (GTID) 来强化数据库的主备一致性,故障恢复,以及容错能力。
官方给的:A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (master).
所以可能是因为在一个数据库里面唯一,但是当导入其他的库就有可能重复。所有会有一个提醒。

可以通过添加–set-gtid-purged=off  或者–gtid-mode=OFF这两个参数设置。
个人认为是在导入库中重新生产GTID,而不用原来的。

4,导出的命令
/wls/mysql/bin/mysqldump -uroot –socket=/wls/mysql/run/mysql.sock –port=3309 –set-gtid-purged=OFF hm_stg ofmucroom ofmucmember ofmucaffiliation>exptab1_hm_stg_3309.sql

hm_stg为导出的database,如果前面加-d表示只导出database或者table的结构

5,导入
进入相应的database
source exptab1_hm_stg_3309.sql
———————
作者:xionglang7
来源:CSDN
原文:https://blog.csdn.net/xionglang7/article/details/38321699
版权声明:本文为博主原创文章,转载请附上博文链接!

mac mysql: error You must reset your password using ALTER USER statement before executing this statement.

安装完mysql 之后,登陆以后,不管运行任何命令,总是提示这个

step 1: SET PASSWORD = PASSWORD(‘your new password’);

step 2: ALTER USER ‘root’@’localhost’ PASSWORD EXPIRE NEVER;

step 3: flush privileges;

完成以上三步退出再登,使用新设置的密码就行了,以上除了红色的自己修改成新密码外,其他原样输入即可

参考1: https://dev.mysql.com/doc/refman/5.6/en/alter-user.html

参考2: http://dev.mysql.com/doc/refman/5.7/en/password-expiration-policy.html

参考3: http://stackoverflow.com/questions/33467337/reset-mysql-root-password-using-alter-user-statement-after-install-on-mac

mysqldump工具的详细介绍

说明:mysqldump是数据库用来备份和数据转移的一个工具,一般在数据量很小的时候(几个G)可以用于备份。当数据量比较大的情况下,就不建议用mysqldump工具进行备份了;mysqldump是一个很好用的mysql数据转移工具,具有兼容强强、跨版本等特点;

一、数据库的导出

导出对象说明:

mysqldump可以针对单个表、多个表、单个数据库、多个数据库、所有数据库进行导出的操作

shell> mysqldump [options] db_name [tbl_name …] //导出指定数据库或单个表

shell> mysqldump [options] –databases db_name … //导出多个数据库

shell> mysqldump [options] –all-databases //导出所有

 

导出选项:在操作系统运行 mysqldump –help即可查看mysqldump的所有可选选项,以下是常用的选项介绍

 

 

–all-databases, -A: 备份所有数据库

–databases, -B: 用于备份多个数据库,如果没有该选项,mysqldump 把第一个名字参数作为数据库名,后面的作为表名。使用该选项,mysqldump 把每个名字都当作为数据库名。

–force, -f:即使发现sql错误,仍然继续备份

–host=host_name, -h host_name:备份主机名,默认为localhost

–no-data, -d:只导出表结构

–password[=password], -p[password]:密码

–port=port_num, -P port_num:制定TCP/IP连接时的端口号

–quick, -q:快速导出

–tables:覆盖 –databases or -B选项,后面所跟参数被视作表名-

–user=user_name, -u user_name:用户名

–xml, -X:导出为xml文件
 

1、导出数据库为test下面表名为b的数据

mysqldump -uroot -h 127.0.0.1  test b > b_test.sql

 

2、导出数据库为test的所有数据

mysqldump -uroot -h 127.0.0.1 test >test.sql

 

3、导出数据库名为test和retail的数据库

mysqldump -uroot -h 127.0.0.1  -B test retail > /data/db.sql

 

4、导出所有的数据库

mysqldump -uroot -h127.0.0.1 -A > /data/all.sql

 

5、导出数据库的结构(以上脚本添加选项 -d)

mysqldump -uroot -h 127.0.0.1  test b  -d> b_test.sql

 

二、数据库的导入

1、导入的方式

mysql命令行导入:

mysql> source /data/test.sql

系统命令行导入:

mysql -uroot -p </data/test.sql

 

2、还原单个数据库(需指定数据库)

mysql >use test

mysql >source /data/test.sql

 

3、还原多个数据库(不需要指定数据库)

mysql -uroot -p </data/all.sql