MySQL Problem summary

1,MySQL master and slave skipped an error in the transaction

#停止slave进程stop slave, #跳过一个事务set global sql_slave_skip_counter=1; #启动slave进程start slave;

2. Reclaim Permissions

#回收超级权限revoke  grant option on *.* (Library. Table)  from admin (user name); #回收权限, only Read permission is reserved update  ' MySQL '. ' User '  SET  ' select_priv ' = ' Y ',  ' insert_priv ' = ' n ',  ' update_priv ' = ' n ',  ' Delete_ Priv ' = ' n ',  ' create_priv ' = ' n ',  ' drop_priv ' = ' n ',  ' reload_priv ' = ' n ',  ' shutdown_priv ' = ' n ',   ' process_priv ' = ' n ',  ' file_priv ' = ' n ',  ' grant_priv ' = ' n ',  ' references_priv ' = ' n ',  ' Index _priv ' = ' n ',  ' alter_priv ' = ' n ',  ' show_db_priv ' = ' n ',  ' super_priv ' = ' n ',  ' Create_tmp_table_priv ' = ' n ',  ' lock_tables_priv ' = ' n ',  ' execute_priv ' = ' n ',  ' repl_slave_priv ' = ' n ',  ' Repl_client_ Priv ' = ' n ',  ' create_view_priv ' = ' n ',  ' show_view_priv ' = ' n ',  ' create_routine_priv ' = ' n ',  ' Alter _routine_priv ' = ' n ',  ' create_user_priv ' = ' n ',  ' event_priv ' = ' n ',  ' trigger_priv ' = ' n ',  ' Create_ Tablespace_priv ' = ' N ',  ' ssl_type ' = ',  ' ssl_cipher ' = ',  ' x509_issuer ' = ',  ' x509_subject ' = ',   ' Max_questioNS ' = ' 0 ',  ' max_updates ' = ' 0 ',  ' max_connections ' = ' 0 ',  ' max_user_connections ' = ' 0 ',  ' plugin ' = ' Mysql_native_password ',  ' authentication_string ' = ',  ' password_expired ' = ' N '  WHERE  (' Host ' = ') % ')  AND  (' User ' = ' admin ');

3, MySQL master-slave replication problem of a solution

#1, view master-slave replication status

Mysql>show slave Status\g

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M01/8D/56/wKiom1iYHe6inHrBAAIJ0tqODBo124.jpg-wh_500x0-wm_3 -wmp_4-s_2189457438.jpg "title=" view MySQL master-slave status "alt=" Wkiom1iyhe6inhrbaaij0tqodbo124.jpg-wh_50 "/>

#2, using Xtrabackup for backup and recovery

Xtrabackup is a backup tool from Percona, which supports on-line hot standby and does not affect data read and write when backing up.  Two tools are available, Xtrabackup and Innobackupex. Xtrabackup can only back up InnoDB and xtradb two data tables, not MYISAM data tables. While the Innobackupex encapsulates the xtrabackup, it can back up both the InnoDB and MyISAM, but it needs to add a global read lock when the backup is MyISAM.

#2.1, installation procedure

installation of Xtrabackup needs to be installed on the master-slave server, and the software version installed on the master-slave server must be consistent .

#2.1.1, configure Epel source

~]# cat/etc/yum.repos.d/myepel.repo[myepel]name=zhedabaseurl=http://mirrors.zju.edu.cn/epel/6/x86_64/gpgcheck=0enabled=1

#2.1.2, installation method

1, download the Percona yum source for installation. ~]# Yum Install http://www.percona.com/downloads/percona-release/redhat/0.1-3/ The percona-release-0.1-3.noarch.rpm query corresponds to the RPM package provided by the Percona Yum source. ~] #yum List | grep Percona

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M01/8D/54/wKioL1iYH1TC88MHAAApI9LELNA174.png-wh_500x0-wm_3 -wmp_4-s_471280363.png "title=" 2.png "alt=" Wkiol1iyh1tc88mhaaapi9lelna174.png-wh_50 "/>

You can select one to install based on the version of Xtrabackup you are querying. Install the 2.2 version here. The installation process can be completed with the ~]# yum install Percona-xtrabackup-22–y.

2, download the RPM package for installation. The Epel source needs to be configured beforehand. Use the wget command to download the RPM package directly. Download here for version 2.4. ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.5/binary/redhat/6/x86_64/ percona-xtrabackup-24-2.4.5-1.el6.x86_64.rpm

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M00/8D/56/wKiom1iYIGSgExC4AAAZfhxo5j0798.png-wh_500x0-wm_3 -wmp_4-s_2868737430.png "title=" Download completed Xtrabackup package "alt=" Wkiom1iyigsgexc4aaazfhxo5j0798.png-wh_50 "/>

The download is complete and can be installed directly. ~]# Yum Install percona-xtrabackup-24-2.4.5-1.el6.x86_64.rpm–y installation is complete and ready to use.

#2.2. Backing up the main library data

#设置主库全局只读mysql >set Global Read_only=1, #开始备份 ~] #innobackupex--defaults-file=/etc/my.cnf--parallel=4/tmp/ mysqlback/>/dev/null 2>&1 Backup parameter resolution:--DEFAULTS-FILE=/ETC/MY.CNF indicates the MySQL profile location at backup--parallel=4 How many threads are open for backup/t mp/mysqlback/the target directory of the backup, usually the previous backup result of the directory is as follows: The backup results are stored in a timestamp directory. As shown in.

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M02/8D/56/wKiom1iYI9XTDQTJAAAXy8Wu3Dc168.png-wh_500x0-wm_3 -wmp_4-s_1268788750.png "title=" xtrabackup Backup Data Directory "alt=" Wkiom1iyi9xtdqtjaaaxy8wu3dc168.png-wh_50 "/>

#2.3. Uploading the backed up data to a Slave library server

Copy has backed up the completed data to the slave library server. ~]# cd/tmp/mysqlback && scp-r 2017-01-14_00-14-11 10.74.244.33:/tmp/mysqlback/

#2.4, recovering from a library server

Stop MySQL from the library service.   ~]# service mysqld stop Delete data from the database data directory.   ~]# cd /data/mysqldata && rm –rf * transactions that were not completed before the backup was committed.   ~]# cd /tmp/mysqlback && innobackupex --apply-log 2017-01-14 _00-14-11 restores the backup data to the data directory from the library.   ~]# cd /tmp/mysqlback && innobackupex --copy-back 2017-01-14 _00-14-11 Modify the permissions from the library MySQL data directory.   ~]# cd /data && chown -R mysql:mysql  Mysqldata starts the MySQL database from the library server.   ~]# service mysqld  Start------------------------The above operation is performed from the library------------------------------------create an account with copy permissions on the main library. mysql>grant replication slave, replication client on *.* to  ' Repluser ' @ ' from the Library IP address ' identified by  ' replpass ';   parse:     from the library's IP address, You can also specify an IP segment by using a wildcard character. View the location of the current binary log on the main library.   mysql>show master status;

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M00/8D/54/wKioL1iYJNLDEKkUAAAb-mUXkpE561.png-wh_500x0-wm_3 -wmp_4-s_2791724056.png "title=" Main Library View binary log location "alt=" Wkiol1iyjnldekkuaaab-muxkpe561.png-wh_50 "/>

Point to the binary log that provides the main library from the library. Mysql>change Master to master_host= ' main Library IP address ', master_user= ' repluser ', master_password= ' Replpass ', master_log_file=  ' master-bin.000021 ', master_log_pos=58469965; Resolution: The IP address of the MySQL master library repluser the account with the Copy permission replpass the password with the copy permission is performed from the library to see if the current replication process is started. The replication process was not started when it was just recovering from the library.  Start the replication process from the library.  mysql> start slave, cancels the global read-only of the main library. Mysql>set Global read_only=0;

4. mysql Database backup

#将数据库备份至一个文件中mysql  -e  "show databases;"  -u (username)  -p -h (database IP address)  | grep -Ev  "database|information_schema|test| Performance_schema (remove these libraries without backup) " | xargs mysqldump -u (user name)  -p -h (database IP address)  -- databases > mysql_dump_tencent.sql# database backup for i in  ' mysql -h (database IP address)  -u (username)  -p (password)  -e  "show databases;"  | grep -v ^+ | egrep -v  ' (information_schema|performance_schema|test| mysql| Database) ';d o        /usr/bin/mysqldump -h (db IP address)  -u (username)  -p (password)  -q --database ${i} > /tmp/backup/${i}.sql         if [ $? == 0 ];then                 echo  "backup omc_trunk ${date_time}  $i  databaSe ok " >> /tmp/backup/backup.log        else                 echo  " backup omc_trunk ${date_time}  $i  database error " >> /tmp/backup/ backup.log        fidone# to compress the backed-up directory cd /tmp/ &&  /bin/tar zcvf backup.tar.gz backup

This article is from "Angry Juan fan IRD" blog, reprint please contact the author!

MySQL Problem summary