存档

‘Mysql数据库’ 分类的存档

MySQL重置root密码方法

2017年2月15日 评论已被关闭

MySQL重置root密码方法

/etc/init.d/mysqld stop

mysqld –skip-grant-tables &

mysql -u root

update mysql.user set password=PASSWORD(‘root’) where User=’root’;

flush privileges;

quit

 

MySQL有时候忘记了root密码是一件伤感的事。这里提供Windows 和 Linux 下的密码重置方法。

Windows:

1.以系统管理员身份登陆系统。

2.打开cmd—–net start 查看mysql是否启动。启动的话就停止net stop mysql.

3.我的mysql安装在d:\usr\local\mysql4\bin下。

4.跳过权限检查启动mysql.

d:\usr\local\mysql\bin\mysqld-nt –skip-grant-tables

5.重新打开cmd。进到d:\usr\local\mysql4\bin下:

d:\usr\local\mysql\bin\mysqladmin -u root flush-privileges password “newpassword”

d:\usr\local\mysql\bin\mysqladmin -u root -p shutdown  这句提示你重新输密码。

6.在cmd里net start mysql

7.搞定了。

Linux:

MySQL root密码的恢复方法之一

如果忘记了MySQL root密码,可以用以下方法重新设置:

1.KILL掉系统里的MySQL进程;

killall -TERM MySQLd

2.用以下命令启动MySQL,以不检查权限的方式启动;

safe_MySQLd –skip-grant-tables &

3.然后用空密码方式使用root用户登录 MySQL;

MySQL -u root

4.修改root用户的密码;

MySQL> update MySQL.user set password=PASSWORD(‘新密码’) where User=’root’;

MySQL> flush privileges;

MySQL> quit

重新启动MySQL,就可以使用新密码登录了。

MySQLroot密码的恢复方法二

有可能你的系统没有 safe_MySQLd 程序(比如我现在用的 ubuntu操作系统, apt-get安装的MySQL) , 下面方法可以恢复

1.停止MySQLd;

sudo /etc/init.d/mysql stop

(您可能有其它的方法,总之停止MySQLd的运行就可以了)

2.用以下命令启动MySQL,以不检查权限的方式启动;

mysqld –skip-grant-tables &

3.然后用空密码方式使用root用户登录 MySQL;

MySQL -u root

4.修改root用户的密码;

MySQL> update MySQL.user set password=PASSWORD(‘newpassword’) where User=’root’;

MySQL> flush privileges;

MySQL> quit

重新启动MySQL

/etc/init.d/MySQL restart

就可以使用新密码 newpassword 登录了。

 

 

[root@TEST ~]# /etc/init.d/mysqld stop

Shutting down MySQL. SUCCESS!

[root@TEST ~]# mysqld –skip-grant-tables &

[2] 2425

170205 16:49:56 [Note] –secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled

170205 16:49:56 [Note] mysqld (mysqld 5.5.53-log) starting as process 2425 …

[root@TEST ~]# mysql -u root

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.5.53-log MySQL Community Server (GPL)

 

Copyright (c) 2000, 2016, 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 [(none)]>

MySQL [(none)]> update mysql.user set password=PASSWORD(‘root’) where User=’root’;

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2  Changed: 2  Warnings: 0

 

MySQL [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

 

MySQL [(none)]> quit

Bye

[2]+  Exit 1                  mysqld –skip-grant-tables

[root@TEST ~]# /etc/init.d/mysqld start

Starting MySQL SUCCESS!

分类: Mysql数据库 标签:

Mysql 5.7 root密码修改

2017年2月6日 评论已被关闭

Mysql 5.7 root密码修改

MySQL管理者密码设置或修改:

依据官方说明5.6以后版本,第一次启动时会在root目录下生产一个随机密码,文件名.mysql_secret。

[root@bright ~]# cat /root/.mysql_secret

# Password set for user ‘root@localhost’ at 2015-03-27 23:12:10

:Jj+FTiqvyrF

[root@bright ~]# cd /usr/local/mysql/bin/

[root@bright bin]# ./mysqladmin -u root -h localhost password ‘123456’ -p

Enter password: #此行输入.mysql_secret里第二行内容

mysqladmin: [Warning] Using a password on the command line interface can be insecure.

Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

官方的方式,笔者无论是否使用–skip-grant-tables启动mysql都测试失败,亲们可以测试:

shell>mysql -uroot -p’password’ #password即.mysql_secret里的密码

mysql>SET PASSWORD = PASSWORD(‘newpasswd’);

 

旧版本,安装后ROOT无密码,按如下操作:

方法一:

shell>service mysqld stop #停止mysql服务

shell>mysqld_safe –skip-grant-tables & #以不启用grant-tables模式启动mysql

shell>mysql -uroot -p #输入命令回车进入,出现输入密码提示直接回车。

mysql>use mysql;

mysql>update user set password=PASSWORD(“123456″)where user=”root”; #更改密码为 newpassord

mysql>flush privileges; #更新权限

mysql>quit #退出

方法二:

shell>service mysqld stop #停止mysql服务

shell>mysqld_safe –skip-grant-tables & #以不启用grant-tables模式启动mysql

shell>mysql -uroot -p #输入命令回车进入,出现输入密码提示直接回车。

mysql > set password for root@localhost = password(‘mysqlroot’);

方法三:

shell>/path/mysqladmin -u UserName -h Host password ‘new_password’ -p

 

分类: Mysql数据库 标签:

安装mysql 出现:Fatal error: Can’t open and lock privilege tables: Table ‘mysql.user’ doesn’t exist

2017年2月3日 评论已被关闭

安装mysql 出现:Fatal error: Can’t open and lock privilege tables: Table ‘mysql.user’ doesn’t exist
来源:http://blog.csdn.net/dapeng0112/article/details/37053407

本来初始化配置是这样的:

scripts/mysql_install_db –basedir=/usr/local/mysql –datadir=/usr/local/mysql/data –user=mysql

出现了

[root@localhost mysql-5.6.14]# service mysql restart
ERROR! MySQL server PID file could not be found!
Starting MySQL. ERROR! The server quit without updating PID file (/var/lib/mysql/localhost.localdomain.pid).

在日志中出现了如下错误:

Can’t open and lock privilege tables: Table ‘mysql.user’ doesn’t exist

后来采用了下面的语句就可以了:

scripts/mysql_install_db –basedir=/usr/local/mysql –datadir=/usr/local/mysql/data –user=mysql –ldata=/var/lib/mysql

分类: Mysql数据库 标签:

mysql备份与恢复详解

2017年1月23日 评论已被关闭

mysql备份与恢复详解

本篇文章是对mysql的备份与恢复进行了详细的分析介绍,需要的朋友参考下

MYSQL的备份有多少种,请简要的描述:
数据库分逻辑备份\物理备份
物理备份又分冷备和热备
A.直接拷贝数据文件到安全地方进行保存
B.使用MYSQLHOSTCOPY备分数据
C.使用MYSQLDUMP备份数据
D.使用MYSQL的同步复制,实现数据实时数据同步备份
常用的逻辑备份主要就是两种:一种是将数据生成为可以完全重现当前数据库中的数据的insert语句,另一种是将数据通过逻辑备份软件,将数据库表的数据以特定分隔符进行分割后记录在文本中。
对于第一种生成insert语句来说我们可以直接使用mysql自带的工具mysqldump来完成。这种方式不好之处在于可能导致数据的不一致,或是不完整。解决办法:一个是通过在数据库系统中加入写入锁,只提供数据库的查询服务;第二种是对于支持事务的存储引擎来说,INNODB BDB可以通过将整个备份过程控制在一个事务中,来达到备份数据的一致性和完整性;并且可以不用影响到数据库的正常运行。
恢复方式则是通过mysql<backup.sql直接运行的。
第二种直接生成数据格式。占用的空间小,数据格式清晰。可是没有数据库结构的脚本。不容易控制
实现方式:通过select******* to outfile from***命令来实现。恢复方式就是通过,load data infile和mysqlimport命令来做。
这一过程相当的复杂需要实时的进行恢复测试,保证备份数据是可用的
数据库的物理备份,主要的对象是数据库的物理数据文件,日志文件以及配置文件等。
物理数据文件有哪些呢?
一是日志文件6大类:错误日志error Log、二进制日志binary Log、更新日志 update log、查询日志 query log、慢查询日志 slow query log、innodb的redo日志。
二是数据文件?对于myisam来说的话,.frm表结构信息.myd数据信息.myi数据的索引信息。对于Innodb来说的话.ibd文件(独享表空间)和.ibdata(共享表空间)文件。
三是replication文件?master.info存储在slave端的数据目录下,存放了slave和master的相关信息,relay log和 relay log index主要存储了I/O进程从Master端读取到的binary log信息,然后由slave端的SQL线程从该binary log中读取解析过的日志信息,转化成master所能执行的query语句。index则是存放binarylog的路径也就是目录文件。
四是系统文件?如my.cnf、pid文件是mysqld应用程序中的一个进程文件存放自己的进程id还有就是socket文件它只有在linux下才有的,可以不通过tcp/ip网络协议直接连接mysql

如果是做冷备的话,直接复制所有的数据文件和日志文件到备份集存放的地方,

热备的方法针对不多的数据库有不同的方案
对于myisam存储引擎来说,做法就是给数据库表加锁来阻止写操作,可以直接复制物理文件,或者也可以通过mysql专门的mysqlhotcopy(原理就是现锁住表,然后进行操作)程序来完成相应的备份任务。
flush tables with read lock
cp -R test /tmp/backup/test
unlock tables;

innodb数据库引擎来说,有一款商业软件ibbackup,在线物理备份功能。还有一款开源的工具xtrabackup,
如果在备份过程中,把INNODB数据文件备份完成后,会锁住整个库,并开始复制MYISAM等非事务引擎的数据和.frm;所以如果你拥有比较多的MYISAM表,锁库的时候会持续很长。如果是在主库上运行,千万注意。

同样也只能通过xtrabackup进行增量的备份,其实这一工具只是备份innobd的日志信息。
4 xtrabackup/ibbackup
xtrabackup –backup –datadir=/var/lib/mysql/ –target-
dir=/data/backups/mysql/
xtrabackup –backup –defaults-file=/etc/my.cnf –target-
dir=/data/backups/mysql/
mysql的备份方式

1.mysqldump
效率比较低,备份和还原的速度都很慢,任何数据插入和更新操作都会被挂起

2.mysqlhotcopy
mysqlhotcopy 是专门针对myisam 数据表进行备份,备份的过程中,任何数据插入和更新操作都会被挂起

3.准备一台从服务器,专门做备份(master-slave方式)

4.xtrabackup 是 percona 的一个开源项目,可热备份innodb ,XtraDB,MyISAM(会锁表)

Xtrabackup有两个主要的工具:xtrabackup、innobackupex
xtrabackup只能备份InnoDB和XtraDB两种数据表,而不能备份MyISAM数据表
innobackupex-1.5.1则封装了xtrabackup,是一个脚本封装,所以能同时备份处理innodb和myisam,但在处理myisam时需要加一个读锁
Øxtra备份原理
记住开始时候的LSN号,然后开始拷备文件,同时运
行一个后台进程监视重做日志,并将变化拷备下来到
xtrabackup_logfile。
innobackupex可以备份myisam表和frm文件。当
xtrabackup结束后,执行flush tables with read lock,避
免数据更改,然后刷新所有myisam表到磁盘。拷备结
束后,释放锁。
mysqlbinlog也是一个恢复工具,是基于时间点来处理二进制文件的
Ø备份:直接拷备
Ø还原:
ü时间点还原:mysqlbinlog –stop-date=”2005-04-20 9:59:59″
/var/log/mysql/bin.123456 | mysql -u root –pmypwd
mysqlbinlog –start-date=”2005-04-20 10:01:00″
/var/log/mysql/bin.123456| mysql -u root –pmypwd
Ø日志点还原:
mysqlbinlog –stop-position=”368312″ /var/log/mysql/bin.123456 \
| mysql -u root -pmypwd
mysqlbinlog –start-position=”368315″ /var/log/mysql/bin.123456 \
| mysql -u root -pmypwd \
LVM是 Logical Volume Manager(逻辑卷管理)的简写,它是Linux环境下对磁盘分区进行管理的一种机制。
LSN定义:
日志序号
日志序号 (LSN) 标识特定日志文件记录在日志文件中的位置。
LSN 由 DB2® 产品中的许多组件用来维护数据库一致性和完整性。除其他作用外,LSN 还对于分区数据库环境中的落实和回滚操作、崩溃和前滚恢复以及数据库操作同步起非常重要的作用。
日志文件中 LSN 的增长率与数据库活动直接相关联。也就是说,随着事务发生并且条目被写入日志文件,LSN 会不断增大。数据库中的活动越多,LSN 增长得越快。
日志序号的上限
在 DB2 V9.5 和较早版本中,日志序号 (LSN) 是 6 字节数字。从 FP3 开始,LSN 的范围在 0x0000 0000 0000(首次创建数据库时)到 0xFFFF 0000 0000(约 256 太字节)之间。在 FP3 之前,上限为 0xFFFF FFFF FFFF。随着记录被添加到日志文件中,LSN 在数据库生命期不断增长。

分类: Mysql数据库 标签:

linux下导入、导出mysql数据库命令

2017年1月23日 评论已被关闭

linux下导入、导出mysql数据库命令
http://www.cnblogs.com/jiunadianshi/archive/2011/04/20/2022334.html
一、导出数据库用mysqldump命令(注意mysql的安装路径,即此命令的路径):
1、导出数据和表结构:
mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql
#/usr/local/mysql/bin/ mysqldump -uroot -p abc > abc.sql
敲回车后会提示输入密码

2、只导出表结构
mysqldump -u用户名 -p密码 -d 数据库名 > 数据库名.sql
#/usr/local/mysql/bin/ mysqldump -uroot -p -d abc > abc.sql

注:/usr/local/mysql/bin/ —> mysql的data目录
二、导入数据库
1、首先建空数据库
mysql>create database abc;

2、导入数据库
方法一:
(1)选择数据库
mysql>use abc;
(2)设置数据库编码
mysql>set names utf8;
(3)导入数据(注意sql文件的路径)
mysql>source /home/abc/abc.sql;
方法二:
mysql -u用户名 -p密码 数据库名 < 数据库名.sql
#mysql -uabc_f -p abc < abc.sql

建议使用第二种方法导入。

注意:有命令行模式,有sql命令

分类: Mysql数据库 标签:

MySQL 5.6 警告信息 command line interface can be insecure 修复

2017年1月23日 评论已被关闭

MySQL 5.6 警告信息 command line interface can be insecure 修复
http://880314.blog.51cto.com/4008847/1348413
MySQL 5.6 警告信息 command line interface can be insecure 修复

在命令行输入密码,就会提示这些安全警告信息。
Warning: Using a password on the command line interface can be insecure.

注: mysql -u root -pPASSWORD 或 mysqldump -u root -pPASSWORD 都会输出这样的警告信息.
1、针对mysql
mysql -u root -pPASSWORD 改成mysql -u root -p 在输入密码即可.

2、mysqldump就比较麻烦了,通常都写在scripts脚本中。

解决方法:
对于 mysqldump 要如何避免出现(Warning: Using a password on the command line interface can be insecure.) 警告信息呢?

vim /etc/mysql/my.cnf
[mysqldump]
user=your_backup_user_name
password=your_backup_password

修改完配置文件后, 只需要执行mysqldump 脚本就可以了。备份脚本中不需要涉及用户名密码相关信息。

分类: Mysql数据库 标签:

mysql命令行命令补全

2017年1月23日 评论已被关闭
分类: Mysql数据库 标签:

MySQL表结构为InnoDB类型从ibd文件恢复

2017年1月18日 评论已被关闭

MySQL表结构为InnoDB类型从ibd文件恢复
http://blog.csdn.net/xiewenbo/article/details/49277505
数据客户的机器系统异常关机,重启后MySQL数据库不能正常启动,重装系统后发现数据库文件损坏,悲催的是客户数据库没有进行及时备份,只能想办法从数据库文件当中恢复,查找资料,试验各种方法,确认下面步骤可行:

一、找回表结构,如果表结构没有丢失直接到下一步

a、先创建一个数据库,这个数据库必须是没有表和任何操作的。

b、创建一个表结构,和要恢复的表名是一样的。表里的字段无所谓。一定要是innodb引擎的。CREATE TABLE `test`( `testID` bigint(20)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

c、关闭mysql, service mysqld stop;

d、用需要恢复的frm文件覆盖刚新建的frm文件;

e、修改my.ini 里 innodb_force_recovery=1 , 如果不成修改为 2,3,4,5,6。

f、 启动mysql,service mysqld start;show create table test就能够看到表结构信息了。

二、找回数据

a、建立一个数据库,根据上面导出的创建表的sql执行创建表。

b、找到记录点。先要把当前数据库的表空间废弃掉,使当前ibd的数据文件和frm分离。 ALTER TABLE test DISCARD TABLESPACE;

c、把之前要恢复的 .ibd文件复制到新的表结构文件夹下。 使当前的ibd 和frm发生关系。ALTER TABLE test IMPORT TABLESPACE;

d、将恢复好的数据导出就行了

分类: Mysql数据库 标签:

查看mysql主从配置的状态及修正 slave不启动问题

2016年12月16日 评论已被关闭

查看mysql主从配置的状态及修正 slave不启动问题
http://blog.chinaunix.net/uid-24426415-id-77316.html
1、查看master的状态
show master status; //Position不应该为0
show processlist;
//state状态应该为Has sent all binlog to slave; waiting for binlog to be updated
2、查看slave状态
show slave status;
//Slave_IO_Running 与 Slave_SQL_Running 状态都要为Yes
show processlist;
//应该有两行state值为:
Has read all relay log; waiting for the slave I/O thread to update it
Waiting for master to send event

3、错误日志
MySQL安装目录 /usr/local/mysql
MySQL日志目录 /usr/local/mysql/data/ 形如,Hostname.err

4、Change master to
如果从库的Slave未启动,Slave_IO_Running为NO。
可能是主库是的master的信息有变化,
查看主库show master status;
记录下File,Position字段,假设为‘mysql-bin.000004’,98;
在从库执行:
mysql>stop slave;
mysql>change master to master_log_file=’mysql-bin.000004′,master_log_pos=98;
mysql>start slave;

5、SET global sql_slave_skip_counter=n;
如果从库的slave_sql_running为NO。
Err文件中记录:
Slave:Error “Duplicate entry ‘1’ for key 1” on query…..
可能是master未向slave同步成功,但slave中已经有了记录。造成的冲突可以在从库上执行
set global sql_slave_skip_counter=n;
跳过几步。再restart slave就可以了。

6、同步错误处理
发现mysql slave服务器经常因为一些特殊字符或者符号产生的更新语句报错,整个同步也会因此而卡在那,最初的办法只是手动去出错的机器执行下面三条SQL语句,跳过错误即可。
mysql>slave stop;
mysql>set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql>slave start;

PS:本人多次遇到从数据库的同步进程自动停掉的问题,有时简单通过slave stop,slave start即可解决。有时slave start启动后又会自动停掉,这时使用 change master重设主数据库信息的方式解决了问题。

说明:
Slave_IO_Running:连接到主库,并读取主库的日志到本地,生成本地日志文件
Slave_SQL_Running:读取本地日志文件,并执行日志里的SQL命令。

分类: Mysql数据库 标签:

MySQL主从同步总结

2016年12月16日 评论已被关闭

MySQL主从同步总结
https://www.mawenbao.com/note/mysql-replication-summary.html
菜鸟DBA的MySQL主从同步总结,若无特别说明,以下内容均基于MySQL 5.5。

工作中要维护一批主-从架构的MySQL服务器,前段时间遇到了一个很奇怪的同步延迟问题,纠结了挺久,后来发现是配置方面的问题。这次干脆全面总结一下,以防日后纠结,如果有任何问题,请不吝指正。

准备工作

在使用MySQL同步之前,务必仔细阅读对应版本的MySQL参考手册的相关部分。

主从同步简介

MySQL同步的流程大致如下:

主服务器(master)将变更事件(更新、删除、表结构改变等等)写入二进制日志(master log)。
从服务器(slave)的IO线程从主服务器(binlog dump线程)获取二进制日志,并在本地保存一份自己的二进制日志(relay log)
从服务器的SQL线程读取本地日志(relay log),并重演变更事件。
二进制日志主要有三种格式: 基于SQL语句的同步(SBR),基于行的同步(RBR)和这两种的混合格式,MySQL 5.1.29之后默认使用基于语句的同步格式。

主服务器会为每个连接上来的从服务器创建单独的binlog dump线程来发送变更事件。

在MySQL 5.5及之前的版本上,从服务器使用一个IO线程和一个SQL线程来进行同步,从MySQL 5.6.3之后,可以通过配置slave-parallel-workers来启用多线程同步。需要说明的是,MySQL 5.6的多线程同步仅在有多个数据库时才有明显的性能提升,因为按照官方文档的说明,每个数据库同一时间只能有一个worker线程来进行工作。简单的说,如果只有一个数据库,那MySQL 5.6的多线程同步根本没用。不过,MySQL 5.7.2之后,可以通过配置slave-parallel-type为LOGICAL_CLOCK来避开这个限制。

关键配置

若无特别说明,以下配置均基于MySQL 5.5。配置修改后用sudo service mysql restart重启MySQL服务。

主服务器配置

主服务器的配置文档。

[mysqld]
server-id=110
log-bin=mysql-bin
binlog_format=mixed
innodb_flush_log_at_trx_commit=1
sync_binlog=1
需要说明的是,innodb_flush_log_at_trx_commit=1并不能完全保证数据不丢失,因为操作系统或云服务提供商可能会有额外的缓存策略。

从服务器配置

从服务器的配置文档。

[mysqld]
server-id=120
# innodb_flush_log_at_trx_commit=2
主服务器的host,用户名和密码等可以直接写在配置里,也可以用CHANGE MASTER TO语句来设置。

将innodb_flush_log_at_trx_commit配置为2可以极大的提高从服务器SQL线程同步的速度,但是如果MySQL进程崩溃可能会丢失1秒左右的数据。(日志文件大概每隔一秒fsync到磁盘上)

常用操作

从头搭建一个主从服务器的流程可参考官方手册或其他教程,以下只是选择性的总结一些常用的SQL语句。

设置同步源

在从服务器上设置主服务器的参数,确保已经正确配置主服务器和从服务器,并且已经在主服务器上创建了用于同步的用户,可参考官方手册。

先暂停从服务器的同步:

mysql> STOP SLAVE;
根据实际情况修改对应的参数:

mysql> CHANGE MASTER TO
MASTER_HOST=’xxx.xxx.xxx.xxx’,
MASTER_PORT=’3306′,
MASTER_USER=’replication user name’,
MASTER_PASSWORD=’replication user password’,
MASTER_LOG_FILE=’master binlog file’,
MASTER_LOG_POS=’master binlog file position’;
确认无误后启动同步:

mysql> START SLAVE;
MASTER_LOG_FILE和MASTER_LOG_POS这两个参数的配置,如果是第一次同步,可参考检查主服务器状态; 如果是之前有同步过,可参考检查从服务器状态。

如果CHANGE MASTER TO里没有设置RELAY_LOG_FILE或RELAY_LOG_POS,所有的relay log都会被删除,并从主服务器重新同步。

检查主服务器状态

开启一个MySQL会话

$ mysql -u root -p
关闭所有数据库的所有表并申请一个全局的读锁,防止写数据。

mysql> FLUSH TABLES WITH READ LOCK;
如果是第一次同步,不要关闭上面的MySQL会话(退出会话或关闭连接都会自动释放全局读锁),因为之前没有写二进制日志(没法同步),可以参考参考手册里的方法用mysqldump将数据库的老数据备份并同步到从服务器上。

下面查看master状态

mysql> SHOW MASTER STATUS\G
输出

File: mysql-bin.000002
Position: 107
Binlog_Do_DB:
Binlog_Ignore_DB:
记住上面SHOW MASTER STATUS输出的File和Position,并在从服务器上用CHANGE MASTER TO配置主服务器即可。

最后,从服务器启动同步后,记得在之前运行FLUSH TABLES语句的MySQL会话里释放全局读锁。

mysql> UNLOCK TABLES;
START TRANSACTION不会自动释放全局读锁。[1]

检查从服务器状态

开启一个MySQL会话,然后执行

mysql> SHOW SLAVE STATUS\G
输出

Slave_IO_State: Waiting for master to send event
Master_Host: master1
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 931
Relay_Log_File: slave1-relay-bin.000056
Relay_Log_Pos: 950
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 931
Relay_Log_Space: 1365
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids: 0
下面介绍几个比较关键的参数,全面的说明可阅读参考手册。

Slave_IO_Running: IO线程是否正常运行
Slave_SQL_Running: SQL线程是否正常运行
Master_Log_File: IO线程正在读取的主服务器日志文件
Read_Master_Log_Pos: IO线程正在读取的主服务器日志文件的位置
Relay_Log_File: SQL线程正在读取和执行的本地日志文件
Relay_Log_Pos: SQL线程正在读取和执行的本地日志文件的位置
Relay_Master_Log_File: SQL线程正在重演的事件所在的主服务器日志文件
Exec_Master_Log_Pos: SQL线程正在重演的事件在主服务器日志文件中的位置
Seconds_Behind_Master: 如果网络没有明显的延迟,该参数标志着SQL线程的事件重演速度。如果该值较大且不断递增,调整innodb_flush_log_at_trx_commit会有比较明显的效果,但是可能会在MySQL进程崩溃时丢失数据。
如果需要切换从服务器的同步源,比如要切换到另一台主服务器,需要在SHOW SLAVE STATUS前暂停IO和SQL线程:

mysql> STOP SLAVE;
然后执行SHOW SLAVE STATUS并记录Relay_Master_Log_File和Exec_Master_Log_Pos的值,在CHANGE MASTER TO中设置好同步坐标,最后启动同步:

mysql> START SLAVE;
常见问题

一般性的问题,建议阅读参考手册的Troubleshooting Replication部分。

同步兼容性

根据参考手册的说明,MySQL支持从低版本的服务器同步到下一个版本的服务器,但是如果一个集群中有多个主服务器,那么最多只能有两种不同的MySQL版本。

调查延迟

查看MySQL日志,是否有报错。
检查从服务器状态,对比主服务器的状态判断是IO线程还是SQL线程的问题,如果IO线程延迟,多半是网络问题。
# on slave
mysql> SHOW SLAVE STATUS\G
# on master
mysql> SHOW MASTER STATUS\G
检查MySQL配置,可以尝试调整innodb_flush_log_at_trx_commit。
检查数据库的状态。
mysql> SHOW ENGINE INNODB STATUS\G
检查操作系统的状态。
$ top
$ free -m
$ iostat
$ vmstat
检查MySQL进程的状态。
$ strace -c -f -p

分类: Mysql数据库 标签:

两主机搭建MySQL主从复制后,show slave status显示:Last_IO_Error: error connecting to master ……

2016年12月16日 评论已被关闭

两主机搭建MySQL主从复制后,show slave status显示:Last_IO_Error: error connecting to master ……
http://blog.csdn.net/zyz511919766/article/details/12753025
两台主机A、B搭建MySQL主从复制关系(A为master,B为slave)后,在slave上执行show slave status,结果中显示Last_IO_Error: error connecting to master ‘replication@VMS00782:3306’……

首先查看B的错误日志文件,发现如下错误:
ERROR] Slave I/O: error connecting to master ‘replication@VMS00782:3306’ – retry-time: 60 retries: 2, Error_code: 1045

接着用perror查看上一部获得的错误代码:
perror 1045
输出:MySQL error code 1045 (ER_ACCESS_DENIED_ERROR): Access denied for user ‘%-.48s’@’%-.64s’ (using password: %s)

难道是复制用的账户存在问题??先在A上确认复制用户账户是否存在且是否赋了正确的权限
mysql> show grants for ‘usvr_replication’@’%’;
+—————————————————————————————————————————–+
| Grants for usvr_replication@% |
+—————————————————————————————————————————–+
| GRANT REPLICATION SLAVE ON *.* TO ‘usvr_replication’@’%’ IDENTIFIED BY PASSWORD ‘*F4039654D0AFD80BB0A7775938EFD47ACB809529’ |
+—————————————————————————————————————————–+
1 row in set (0.00 sec)
发现没有问题!

然后,试着从B使用该账户连接至A:
mysql -uusvr_replication -h 192.168.83.35 -p -P55944
输入设置的密码后回车,没连上!!!再次输入,还没连上!!!
看来问题出在这儿了,仔细检查,原来是密码记错了!

试着用正确的密码重建复制关系:
在B上执行:
mysql>stop slave;
mysql>researt slave;
mysql>change master to master_host = ‘VMS00782’,
master_user = ‘replication’,
master_password = ‘ReplPass@123456’,
master_port = 3306,
master_log_file = ‘VMS00782-bin.000001’,
master_log_pos = 120;
mysql>start slave;
mysql>show slave status;
一切正常!!!

需要注意的几个问题:
要在change master to语句的master_password部分使用原始的密码而非散列过的密码。
注意在第一时间查看错误日志文件,可使用perror工具根据错误代码查看具体的错误。

另外几个常见问题:
mysql无法启动:先看错误日志文件中提示的错误,依据错误找原因;查看配置文件中数据目录等配置是否正确;检查MySQL相关目录属主和属组是否正确;查看是否有之前未被正确关闭的mysqld进程仍在运行。
mysql无法连接:先看mysqld进程是否正确启动了;再看提供的连接串是否争取无误。

分类: Mysql数据库 标签:

mysql 数据同步 出现Slave_IO_Running:No问题的解决方法小结

2016年12月16日 评论已被关闭

mysql 数据同步 出现Slave_IO_Running:No问题的解决方法小结
http://blog.csdn.net/mergerly/article/details/50068589
MySQL replication 中slave机器上有两个关键的进程,死一个都不行,一个是slave_sql_running,一个是Slave_IO_Running,一个负责与主机的io通信,一个负责自己的slave mysql进程。
下面写一下,这两个要是有no了,怎么恢复。。

如果是slave_io_running no了,那么就我个人看有三种情况,一个是网络有问题,连接不上,像有一次我用虚拟机搭建replication,使用了nat的网络结构,就是死都连不上,第二个是有可能my.cnf有问题,配置文件怎么写就不说了,网上太多了,最后一个是授权的问题,replication slave和file权限是必须的。如果不怕死就all咯。。

一旦io为no了先看err日志,看看爆什么错,很可能是网络,也有可能是包太大收不了,这个时候主备上改max_allowed_packet这个参数。

如果是slave_sql_running no了,那么也有两种可能,一种是slave机器上这个表中出现了其他的写操作,就是程序写了,这个是会有问题的,今天我想重现,但是有时候会有问题,有时候就没有问题,现在还不是太明了,后面再更新,还有一种占绝大多数可能的是slave进程重启,事务回滚造成的,这也是mysql的一种自我保护的措施,像关键时候只读一样。

这个时候想恢复的话,只要停掉slave,set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;再开一下slave就可以了,这个全局变量赋值为N的意思是:

This statement skips the next N events from the master. This is useful for recovering from replication stops caused by a statement.

This statement is valid only when the slave thread is not running. Otherwise, it produces an error.

呵呵,讲的比我清楚。

MYSQL镜像服务器因错误停止的恢复

下午主服务器,由于一些原因,导致死机,重启后,发现从服务器的数据没有跟上。
配好MYSQL主从也才前几天的事,没多少经验,第一次碰上这问题,有点焦急。不过,自己试了下,还算解决了:)

从服务器上
Master_Log_File: mysqlhxmaster.000007
Read_Master_Log_Pos: 84285377

看一下主服务器:mysqlhxmaster.000007 | 84450528 |
已经过后很多了,确实没跟上。

show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: No

有问题了,Slave_SQL_Running应该是Yes才对。
再往下看,有错误的提示:

Last_Errno: 1053
Last_Error: Query partially completed on the master (error on master: 1053) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; . Query: ‘INSERT INTO hx_stat_record ……(一句SQL语句)’

这里有说明要怎么操作了:)

先stop slave,然后执行了一下提示的语句,再SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

show slave status\G

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

OK了,从服务器也在几分钟内把堆积的log处理完了,两边又同步了:)

从MYSQL服务器Slave_IO_Running: No的解决2

早晨机房意外断电,导致了发现mysql从服务器同步异常。使用以前碰到的Slave_SQL_Running为No的解决办法无效,仍然无法同步。

查看一下状态show slave status
Master_Log_File: mysqlmaster.000079
Read_Master_Log_Pos: 183913228
Relay_Log_File: hx-relay-bin.002934
Relay_Log_Pos: 183913371
Relay_Master_Log_File: mysqlmaster.000079
Slave_IO_Running: No
Slave_SQL_Running: Yes

主服务器show master status\G
File: mysqlmaster.000080
Position: 13818288
Binlog_Do_DB:
Binlog_Ignore_DB: mysql,test

mysql错误日志:
100512 9:13:17 [Note] Slave SQL thread initialized, starting replication in log ‘mysqlmaster.000079’ at position 183913228, relay log ‘./hx-relay-bin.002934’ position: 183913371
100512 9:13:17 [Note] Slave I/O thread: connected to master ‘replicuser@192.168.1.21:3306’, replication started in log ‘mysqlmaster.000079’ at position 183913228
100512 9:13:17 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236)
100512 9:13:17 [ERROR] Got fatal error 1236: ‘Client requested master to start replication from impossible position’ from master when reading data from binary log
100512 9:13:17 [Note] Slave I/O thread exiting, read up to log ‘mysqlmaster.000079′, position 183913228

这次是Slave_IO_Running为No,从日志上来看,服务器读mysqlmaster.000079这个Log的183913228这个位置时发生错误,这个位置不存在,于是无法同步。

查看一下这个Log的最后几行:
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# at 4
#100511 9:35:15 server id 1 end_log_pos 98 Start: binlog v 4, server v 5.0.27-standard-log created 100511 9:35:15
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.

尝试从损坏之前的位置开始
SLAVE STOP;
CHANGE MASTER TO MASTER_LOG_FILE=’mysqlcncnmaster.000079′, MASTER_LOG_POS=183913220;
SLAVE START;
无效!
只好从新的日志开始
SLAVE STOP;
CHANGE MASTER TO MASTER_LOG_FILE=’mysqlcncnmaster.000080’, MASTER_LOG_POS=0;
SLAVE START;
此时Slave_IO_Running恢复为Yes,同步进行了!观察了会儿,没有任何出错迹象,问题解决。

mysql 数据同步 出现Slave_IO_Running:No问题的解决方法小结

2016年12月16日 评论已被关闭

mysql 数据同步 出现Slave_IO_Running:No问题的解决方法小结

http://www.jb51.net/article/27220.htm
mysql replication 中slave机器上有两个关键的进程,死一个都不行,一个是slave_sql_running,一个是Slave_IO_Running,一个负责与主机的io通信,一个负责自己的slave mysql进程。
下面写一下,这两个要是有no了,怎么恢复。。

如果是slave_io_running no了,那么就我个人看有三种情况,一个是网络有问题,连接不上,像有一次我用虚拟机搭建replication,使用了nat的网络结构,就是死都连不上,第二个是有可能my.cnf有问题,配置文件怎么写就不说了,网上太多了,最后一个是授权的问题,replication slave和file权限是必须的。如果不怕死就all咯。。

一旦io为no了先看err日志,看看爆什么错,很可能是网络,也有可能是包太大收不了,这个时候主备上改max_allowed_packet这个参数。

如果是slave_sql_running no了,那么也有两种可能,一种是slave机器上这个表中出现了其他的写操作,就是程序写了,这个是会有问题的,今天我想重现,但是有时候会有问题,有时候就没有问题,现在还不是太明了,后面再更新,还有一种占绝大多数可能的是slave进程重启,事务回滚造成的,这也是mysql的一种自我保护的措施,像关键时候只读一样。

这个时候想恢复的话,只要停掉slave,set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;再开一下slave就可以了,这个全局变量赋值为N的意思是:

This statement skips the next N events from the master. This is useful for recovering from replication stops caused by a statement.

This statement is valid only when the slave thread is not running. Otherwise, it produces an error.

呵呵,讲的比我清楚。

MYSQL镜像服务器因错误停止的恢复

下午主服务器,由于一些原因,导致死机,重启后,发现从服务器的数据没有跟上。
配好MYSQL主从也才前几天的事,没多少经验,第一次碰上这问题,有点焦急。不过,自己试了下,还算解决了:)

从服务器上
Master_Log_File: mysqlhxmaster.000007
Read_Master_Log_Pos: 84285377

看一下主服务器:mysqlhxmaster.000007 | 84450528 |
已经过后很多了,确实没跟上。

show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: No

有问题了,Slave_SQL_Running应该是Yes才对。
再往下看,有错误的提示:

Last_Errno: 1053
Last_Error: Query partially completed on the master (error on master: 1053) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; . Query: ‘INSERT INTO hx_stat_record ……(一句SQL语句)’

这里有说明要怎么操作了:)

先stop slave,然后执行了一下提示的语句,再SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

show slave status\G

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

OK了,从服务器也在几分钟内把堆积的log处理完了,两边又同步了:)

从MYSQL服务器Slave_IO_Running: No的解决2

早晨机房意外断电,导致了发现mysql从服务器同步异常。使用以前碰到的Slave_SQL_Running为No的解决办法无效,仍然无法同步。

查看一下状态show slave status
Master_Log_File: mysqlmaster.000079
Read_Master_Log_Pos: 183913228
Relay_Log_File: hx-relay-bin.002934
Relay_Log_Pos: 183913371
Relay_Master_Log_File: mysqlmaster.000079
Slave_IO_Running: No
Slave_SQL_Running: Yes

主服务器show master status\G
File: mysqlmaster.000080
Position: 13818288
Binlog_Do_DB:
Binlog_Ignore_DB: mysql,test

mysql错误日志:
100512 9:13:17 [Note] Slave SQL thread initialized, starting replication in log ‘mysqlmaster.000079’ at position 183913228, relay log ‘./hx-relay-bin.002934’ position: 183913371
100512 9:13:17 [Note] Slave I/O thread: connected to master ‘replicuser@192.168.1.21:3306’, replication started in log ‘mysqlmaster.000079’ at position 183913228
100512 9:13:17 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236)
100512 9:13:17 [ERROR] Got fatal error 1236: ‘Client requested master to start replication from impossible position’ from master when reading data from binary log
100512 9:13:17 [Note] Slave I/O thread exiting, read up to log ‘mysqlmaster.000079′, position 183913228

这次是Slave_IO_Running为No,从日志上来看,服务器读mysqlmaster.000079这个Log的183913228这个位置时发生错误,这个位置不存在,于是无法同步。

查看一下这个Log的最后几行:
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# at 4
#100511 9:35:15 server id 1 end_log_pos 98 Start: binlog v 4, server v 5.0.27-standard-log created 100511 9:35:15
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.

尝试从损坏之前的位置开始
SLAVE STOP;
CHANGE MASTER TO MASTER_LOG_FILE=’mysqlcncnmaster.000079′, MASTER_LOG_POS=183913220;
SLAVE START;
无效!
只好从新的日志开始
SLAVE STOP;
CHANGE MASTER TO MASTER_LOG_FILE=’mysqlcncnmaster.000080’, MASTER_LOG_POS=0;
SLAVE START;
此时Slave_IO_Running恢复为Yes,同步进行了!观察了会儿,没有任何出错迹象,问题解决。

另外,出现Slave_IO_Running:NO还有一个原因是slave上没有权限读master上的数据。

show slave status\G 详解

2016年12月16日 评论已被关闭

show slave status\G 详解
http://blog.csdn.net/xiaoborui20110806/article/details/36872599
MySQL> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 130.17.180.71
Master_User: cpc
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 116932874
Relay_Log_File: WINDOWS-V2DDMKC-relay-bin.000004
Relay_Log_Pos: 336557
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: repeater
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 108602503
Relay_Log_Space: 8667093
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 4687
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)

******************************************************************************

Slave_IO_State

如果线程正在试图连接到主服务器,正在等待来自主服务器的时间或正在连接到主服务器等

Master_User

被用于连接主服务器的当前用户。

Master_Port

当前的主服务器接口。

Connect_Retry

–master-connect-retry选项的当前值

Master_Log_File

I/O线程当前正在读取的主服务器二进制日志文件的名称。

Read_Master_Log_Pos

在当前的主服务器二进制日志中,I/O线程已经读取的位置。

Relay_Log_File

SQL线程当前正在读取和执行的中继日志文件的名称。

Relay_Log_Pos

在当前的中继日志中,SQL线程已读取和执行的位置。

Relay_Master_Log_File

由SQL线程执行的包含多数近期事件的主服务器二进制日志文件的名称。

Slave_IO_Running

I/O线程是否被启动并成功地连接到主服务器上。

Slave_SQL_Running

SQL线程是否被启动。

Replicate_Do_DB,Replicate_Ignore_DB

使用–replicate-do-db和–replicate-ignore-db选项指定的数据库清单。

Replicate_Do_Table,Replicate_Ignore_Table,Replicate_Wild_Do_Table,Replicate_Wild_Ignore_Table

使用–replicate-do-table,–replicate-ignore-table,–replicate-wild-do-table和–replicate-wild-ignore_table选项指定的表清单。

Last_Errno,Last_Error

被多数最近被执行的查询返回的错误数量和错误消息。错误数量为0并且消息为空字符串意味着“没有错误”。如果Last_Error值不是空值,它也会在从属服务器的错误日志中作为消息显示。

举例说明:

Last_Errno: 1051

Last_Error: error ‘Unknown table ‘z” on query ‘drop table z’

该消息指示,表z曾经存在于在主服务器中并已被取消了,但是它没有在从属服务器中存在过,因此对于从属服务器,DROP TABLE失败。(举例说明,在设置复制时,如果您忘记了把此表拷贝到从属服务器中,则这有可能发生。)

Skip_Counter

最近被使用的用于SQL_SLAVE_SKIP_COUNTER的值。

Exec_Master_Log_Pos

来自主服务器的二进制日志的由SQL线程执行的上一个时间的位置(Relay_Master_Log_File)。在主服务器的二进制日志中的(Relay_Master_Log_File,Exec_Master_Log_Pos)对应于在中继日志中的(Relay_Log_File,Relay_Log_Pos)。

Relay_Log_Space

所有原有的中继日志结合起来的总大小。

Until_Condition,Until_Log_File,Until_Log_Pos

在START SLAVE语句的UNTIL子句中指定的值。

Until_Condition具有以下值:

如果没有指定UNTIL子句,则没有值

如果从属服务器正在读取,直到达到主服务器的二进制日志的给定位置为止,则值为Master

如果从属服务器正在读取,直到达到其中继日志的给定位置为止,则值为Relay

Until_Log_File和Until_Log_Pos用于指示日志文件名和位置值。日志文件名和位置值定义了SQL线程在哪个点中止执行。

Master_SSL_Allowed,Master_SSL_CA_File,Master_SSL_CA_Path,Master_SSL_Cert,Master_SSL_Cipher,Master_SSL_Key

这些字段显示了被从属服务器使用的参数。这些参数用于连接主服务器。

Master_SSL_Allowed具有以下值:

如果允许对主服务器进行SSL连接,则值为Yes

如果不允许对主服务器进行SSL连接,则值为No

如果允许SSL连接,但是从属服务器没有让SSL支持被启用,则值为Ignored。

与SSL有关的字段的值对应于–master-ca,–master-capath,–master-cert,–master-cipher和–master-key选项的值。

Seconds_Behind_Master

本字段是从属服务器“落后”多少的一个指示。当从属SQL线程正在运行时(处理更新),本字段为在主服务器上由此线程执行的最近的一个事件的时间标记开始,已经过的秒数。当此线程被从属服务器I/O线程赶上,并进入闲置状态,等待来自I/O线程的更多的事件时,本字段为零。总之,本字段测量从属服务器SQL线程和从属服务器I/O线程之间的时间差距,单位以秒计。

分类: Mysql数据库 标签:

查看mysql主从配置的状态及修正 slave不启动问题

2016年12月16日 评论已被关闭

查看mysql主从配置的状态及修正 slave不启动问题
http://jiejie.blog.techweb.com.cn/archives/249.html
1、查看master的状态

show master status; //Position不应该为0

show processlist;

//state状态应该为Has sent all binlog to slave; waiting for binlog to be updated

2、查看slave状态

show slave status;

//Slave_IO_Running 与 Slave_SQL_Running 状态都要为Yes

show processlist;

//应该有两行state值为:

Has read all relay log; waiting for the slave I/O thread to update it

Waiting for master to send event

3、错误日志

MySQL安装目录 /usr/local/mysql

MySQL日志目录 /usr/local/mysql/data/ 形如,Hostname.err

4、Change master to

如果从库的Slave未启动,Slave_IO_Running为NO。

可能是主库是的master的信息有变化,

查看主库show master status;

记录下File,Position字段,假设为‘mysql-bin.000004’,98;

在从库执行:

mysql>stop slave;

mysql>change master to master_log_file=’mysql-bin.000004′,master_log_pos=98;

mysql>start slave;

5、SET global sql_slave_skip_counter=n;

如果从库的slave_sql_running为NO。

Err文件中记录:

Slave:Error “Duplicate entry ‘1′ for key 1″ on query…..

可能是master未向slave同步成功,但slave中已经有了记录。造成的冲突可以在从库上执行

set global sql_slave_skip_counter=n;

跳过几步。再restart slave就可以了。

6、同步错误处理

发现mysql slave服务器经常因为一些特殊字符或者符号产生的更新语句报错,整个同步也会因此而卡在那,最初的办法只是手动去出错的机器执行下面三条SQL语句,跳过错误即可。

mysql>slave stop;

mysql>set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

mysql>slave start;

PS:本人多次遇到从数据库的同步进程自动停掉的问题,有时简单通过slave stop,slave start即可解决。有时slave start启动后又会自动停掉,这时使用 change master重设主数据库信息的方式解决了问题。

说明:

Slave_IO_Running:连接到主库,并读取主库的日志到本地,生成本地日志文件

Slave_SQL_Running:读取本地日志文件,并执行日志里的SQL命令。

分类: Mysql数据库 标签:

MySQL 5.6 警告信息 command line interface can be insecure 修复

2016年12月12日 评论已被关闭

MySQL 5.6 警告信息 command line interface can be insecure 修复
http://www.68idc.cn/help/jiabenmake/qita/2014010766686.html
MySQL5.6警告信息commandlineinterfacecanbeinsecure修复在命令行输入密码,就会提示这些安全警告信息。Warning:Usingapasswordonthecommandlineinterfacecanbei
MySQL 5.6 警告信息 command line interface can be insecure 修复

在命令行输入密码,就会提示这些安全警告信息。

Warning: Using a password on the command line interface can be insecure.

注: mysql -u root -pPASSWORD 或 mysqldump -u root -pPASSWORD 都会输出这样的警告信息.

1、针对mysql

mysql -u root -pPASSWORD 改成mysql -u root -p 在输入密码即可.

2、mysqldump就比较麻烦了,通常都写在scripts脚本中。

解决方法:

对于 mysqldump 要如何避免出现(Warning: Using a password on the command line interface can be insecure.) 警告信息呢?

vim /etc/mysql/my.cnf

[mysqldump]

user=your_backup_user_name

password=your_backup_password

修改完配置文件后, 只需要执行mysqldump 脚本就可以了。备份脚本中不需要涉及用户名密码相关信息。

分类: Mysql数据库 标签:

解决MySQL5.6出现”Using a password on the command line interface…”

2016年12月12日 评论已被关闭

解决MySQL5.6出现”Using a password on the command line interface…”

http://www.laozuo.org/6799.html

今天老左有在帮一个网友搬家网站过程中,习惯导出MySQL数据库的时候采用mysqldump命令,但是意外发生了出现”Warning: Using a password on the command line interface can be insecure.”的错误提示,当然数据库肯定也没有能备份下来。这个问题应该是在MySQL5.6+版本的时候就有出现,可能是为了确保数据库的安全性采用的保护机制。

遇到问题那就去解决问题,大概搜索到国内的一些网站,大部分都是复制的,也没有讲的明白,于是还是找老外的信息,于是老左就整理到下面比较全的方法且经过验证是没有问题的。

第一种方法、修改数据库配置文件

1、我们需要修改数据库配置文件,这个要看我们数据库的配置的,有些是在/etc/my.cnf,有些是/etc/my.conf

Warning: Using a password on the command line interface can be insecure.

我们需要在[client]部分添加脚本:

host=localhost
user=数据库用户
password=’数据库密码’

这里参数要修改成我们自己的。

2、采用命令导出和导入数据库

其实在这个时候,我们如果采用”详解使用mysqldump命令备份还原MySQL数据用法整理“介绍的方法也是可以使用的,虽然依旧有错误提示,但是数据库还是可以导出的。您肯定和老左一样是追求细节的人,一点点问题都不能有,但我们可以用下面的命令导出和导入,就没有错误提示。

#导出数据库

mysqldump –defaults-extra-file=/etc/my.cnf database > database.sql

#导入数据库

mysql –defaults-extra-file=/etc/my.cnf database < database.sql

这里我们可以看到上面的命令和以前常用的快速导入和导入命令有所不同了,需要加载我们配置的MYSQL配置文件,这个红色部分要根据我们实际的路径修改。用这样的命令导出备份和导入是没有错误提示的。

第二种方法、利用mysql_config_editor

1、设置加密模式

mysql_config_editor set –login-path=local –host=localhost –user=db_user –password

红色部分是需要修改成我们自己数据库用户名的,回车之后会提示我们输入数据库密码,我们照样输入。

2、执行备份

mysqldump -u db_user -pInsecurePassword my_database | gzip > backup.tar.gz

根据我们数据信息修改用户和用户名和数据库密码,执行备份,这里老左测试还是有错误提示,但数据库是可以备份的。

总之,我们只要实现结果,可以选择以上2种方法其一操作,当然老左也有看到其他的方法,这里就不多分享了,如果有兴趣的可以搜索其他解决方法。

分类: Mysql数据库 标签:

[MySQL FAQ]系列 — 使用mysqldump备份时为什么要加上 -q 参数

2016年12月12日 评论已被关闭

[MySQL FAQ]系列 — 使用mysqldump备份时为什么要加上 -q 参数

http://imysql.com/2015/03/21/mysql-faq-why-turn-on-quick-option.shtml

写在前面:我们在使用mysqldump备份数据时,请一定记住要加上 -q 参数,后果可能是很严重的,不要给自己挖坑哦。到底为什么呢,且听我慢慢道来!

先来看看 mysqldump –help 中,关于 -q 参数的解释:

-q, --quick         Don't buffer query, dump directly to stdout.

简言之,就是说加上 -q 后,不会把SELECT出来的结果放在buffer中,而是直接dump到标准输出中,顶多只是buffer当前行结果,正常情况下是不会超过 max_allowed_packet 限制的,它默认情况下是开启的。

如果关闭该参数,则会把SELECT出来的结果放在本地buffer中,然后再输出给客户端,会消耗更多内存。

在mysqldump.c中也能看到二者的对比(现在流行深入源码,虽然我不是专注开发的,找几行源码能力还尚存,用来装B的,大家知道就好,哈哈):

if (quick)
  res=mysql_use_result(sock);
else
  res=mysql_store_result(sock);

有理论,也要有实践不是,我们来看看在实际场景中,加不加 -q 的区别有多大。

部分备份(启用-q) 部分备份(禁用-q) 完整备份(启用-q) 完整备份(禁用-q)
备份总耗时 27.882秒 22.665秒 277.387秒 217.074秒
占用内存(含swap) 3056KB 2.5GB 3048KB 内存:12GBswap:305MB

可以看到,如果只是备份小量数据,足以放在空闲内存buffer中的话,禁用 -q 会快一些,但如果是大数据集,没办法完全hold在内存buffer中时,就会产生swap,效率反而更差,真是赔了夫人又折兵。

因此,如果使用mysqldump来备份数据时,建议总是加上 -q 参数,避免发生swap反而影响备份效率。

详细过程(有耐心的可以继续往下看)

1、全量备份:备份时不使用 -q 参数

mysqldump --quick=false -Smysql.sock -B yejr --tables t_yejr

#先看下一开始时的状态:
Mem:  32863040k total, 29338704k used,  3524336k free,   227632k buffers
Swap: 16777208k total,    23548k used, 16753660k free,  8200416k cached
PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
21986 root      20   0 6119m 5.9g 2192 S 20.6 18.9   0:21.69 mysqldump

#再看下备份结束后的状态,内存不够用,产生了swap
Mem:  32863040k total, 32521328k used,   341712k free,      440k buffers
Swap: 16777208k total,   336876k used, 16440332k free,   315192k cached
PID   USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+   COMMAND
21986 root      20   0 12.3g  12g  656 R 100.0 39.1   2:23.93 mysqldump

#最后看下备份总耗时
real    4m37.387s
user    2m2.731s
sys     0m24.608s

2、全量备份:备份时启用 -q 参数

mysqldump -Smysql.sock -B yejr --tables t_yejr

#先看下一开始时的状态:
Mem:  32863040k total, 20157476k used, 12705564k free,     4608k buffers
Swap: 16777208k total,        0k used, 16777208k free,   488296k cached

#再看下备份结束后,可以看到,没有使用到swap
Mem:  32863040k total, 32644496k used,   218544k free,      920k buffers
Swap: 16777208k total,        0k used, 16777208k free, 12618740k cached
PID   USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
25234 root      20   0 50880 3048 2192 S 57.6  0.0   2:22.79 mysqldump

#最后看下总耗时统计:
real    3m37.074s
user    2m6.018s
sys     0m17.315s

3、部分备份:备份时不使用 -q 参数

mysqldump -w " id<100000 " -Smysql.sock --quick=false -Smysql.sock -B yejr --tables t_yejr

#看下总耗时
real 0m22.665s
user 0m20.458s
sys 0m2.156s

#再看下mysqldump进程消耗的内存,最高时大概使用了2.5G内存
20619 root      20   0 2571m 2.5g 2208 R 99.9  7.8   0:11.63 mysqldump

4、部分备份:备份时启用 -q 参数

mysqldump -w " id<100000 " -Smysql.sock -Smysql.sock -B yejr --tables t_yejr

#看下总耗时,并没有慢多少
real 0m27.882s
user 0m22.610s
sys 0m0.670s

#再看下mysqldump进程消耗的内存,只占用了极少量内存
19690 root      20   0 50880 3056 2200 S 73.4  0.0   0:06.01 mysqldump
分类: Mysql数据库 标签:

mysql mysqldump 导入导出大文件

2016年12月12日 评论已被关闭

mysql mysqldump 导入导出大文件
https://fukun.org/archives/04261980.html
在本机搭建测试环境时,使用了mysqldump导出了一个数据库文件,将近500M,即使是压缩后也有100M左右,在通过mysqldump导入时,发生了错误: “MySQL server has gone away.”。
个人猜测,发生 MySQL server has gone away 问题的原因很可能就是数据文件过大,导致超时。
所以对mysql进行修改:修改my.ini(在lnix/unix下是my.cnf)文件,加大超时参数

wait_timeout=2888888
如果没有此参数,直接在my.ini/my.cnf文件末尾一行添加上即可。
重启mysql。
最后再次执行导入语句

mysql -uroot -p123456 test_data < D:Xampsqldatatest.sql
注:在使用mysqldump时要注意,客户端mysqldump工具的版本要高于等于服务器的版本。

分类: Mysql数据库 标签: ,

Mysql参数解释—wait_timeout、interactive_timeout

2016年12月9日 评论已被关闭

Mysql参数解释—wait_timeout、interactive_timeout
http://blog.itpub.net/12309491/viewspace-697887/
参数:interactive_timeout、wait_timeout

官方解释:
interactive_timeout
The number of seconds the server waits for activity on an interactive connection before closing it.
等待一个交互进程变成活动状态的最长时间
wait_timeout
The number of seconds the server waits for activity on a noninteractive connection before closing it. This timeout applies only
to TCP/IP and Unix socket file connections, not to connections made using named pipes, or shared memory.
等待一个未交互进程变成活动状态的最长时间

首先理解交互进程,它指的是这个进程正在等待该进程的提交、撤销操作;未交互进程指的是该进程的一个操作已经完成,正在sleep,等待下个操作进行。

实际操作看看这两个参数的效果:
SET GLOBAL interactive_timeout=20;
SET GLOBAL wait_timeout=50;

s1 s2
start transaction
update t set c1 = 1;
等待20s不提交
发现直接被断开
update t set c1 = 1

等待50s
被直接断开