Shulou(Shulou.com)06/01 Report--

This article mainly introduces the technology commonly used in MySQL, the content of the article is carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand the technology commonly used in MySQL.

Deploy the database

Apt-get update

The problem may be reported incorrectly

Rm / var/lib/apt/lists/lock

Dpkg-configure-a

Apt-get install mysql-server

Pop-up box-enter password to confirm password / can also be configured by mysql_secure_installation

Mysql-uroot-pendant 12345'

Master-slave configuration

1. Modify the main configuration file

Vim / etc/mysql/mysql.cnf

[mysqld] log-bin=master-bin / / start MySQL binary log server-id=100 log_bin_index = master-bin.index / / specify a database to record binary logs binlog_do_db = my_data / / specify a database to record binary logs binlog_ignore_db = mysql / / specify a database that does not record binary logs. # bind 127.0.0.1 commented out

2. Log in to the primary CVM to create the account and permissions used from the CVM:

Mysql > grant replication slave on *. * to slave@39.105.177.38 identified by '12345 investors MySQL > flush privileges

Problem authorization this root@39.105.177.38 with single quotation marks will make an error unconfirmed

Authorize to the host that wants to log in

Bind 0.0.0.0

GRANT ALL PRIVILEGES ON *. * TO 'root'@'192.168.1.8' IDENTIFIED BY' www.linuxidc.com' WITH GRANT OPTION

3. Restart mysql

Service mysql restart

4. Check the status

1. Edit the configuration file

[mysqld] log_bin=slave-binserver-id = 2relay-log = slave-relay-binrelay-log-index = slave-relay-bin.index...#bind 127.0.0.1 comment out

2. Restart the service

Service mysql restart

3. Log in to the Slave slave CVM and connect to the Master master CVM.

Mysql > change master to master_host='39.96.73.99',master_user='slave',master_password='12345',master_log_file='master-bin.000001',master_log_pos=154

4. Start Slave data synchronization.

Mysql > start slave

5. View Slave information:

Mysql > show slave status\ G

# synchronization succeeded

If the problem is not successful, the master executes the command from above.

Slave interrupt problem

Error display: ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

Most of the permissions are inconsistent with log pos.

Slave: stop slave reset slave makes the following premise: the slave database and master database data are the same at the moment, otherwise there will be a master error report due to the lack of a certain piece of data: flush logs; show master status; record File, Position. Slave: CHANGE MASTER TO MASTER_LOG_FILE='file',MASTER_LOG_POS=pos; slave start; show slave status\ G

Error

Solution: stop slave

Set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; # skips 1 error on slave

Start slave

Summary of online MYSQL synchronous error fault handling http://blog.itpub.net/15498/viewspace-2134013/

For the third kind, I tried direct three-step https://blog.csdn.net/heng_ji/article/details/51013710.

Uninstall mysql

Sudo apt-get remove mysql-* dpkg-l | grep ^ rc | awk'{print $2}'| sudo xargs dpkg-P / / Clean the residual data

Pop up a dialog box and select yes

Database backup

Note whether you need to uncomment the backup in my.cnf: mysqldump-uroot-p12345-- all-databases-- single-transaction-- master-data=2-- flush-logs > / alidata/ `date +% Fmuri% H% M`-mysql-all.sql

Data recovery

Reduction library mysql-uroot-p12345

< /alidata/backup/mysql-all.sql 导表： mysql -uroot -p12345 进去数据库 use cw100; source /root/crm.sql 在相应表里将外表倒进去 一个完整的备份脚本例子 #!/bin/bash# Program# use mysqldump to Fully backup mysql data per week!# 2017-8-28 huangwei# History# PathBakDir=/var/lib/mysql/backupLogFile=/var/lib/mysql/backup/fullBakLog.logDate=`date +%Y%m%d`Begin=`date +"%Y年%m月%d日 %H:%M:%S"`cd $BakDirDumpFile=$Date.sqlGZDumpFile=$Date.sql.tgzmysqldump -uroot -pcw100SQ18 --all-databases >

$DumpFile/bin/tar-zvcf $GZDumpFile $DumpFile/bin/rm $DumpFile# retains only the database contents of the past four weeks count=$ (ls-l *. Tgz | wc-l) if [$count-ge 5] thenfile=$ (ls-l *. Tgz | awk'{print $9}'| awk 'NR==1') rm-f $filefiLast= `date + "% Y% m% d% H:%M:%S" `echo start: $Begin end: $Last $GZDumpFile succ > > $LogFile#cd $BakDir/daily#/bin/rm-f

View binlog Log

Eg:/usr/bin/mysqlbinlog-no-defaults-base64-output=decode-rows-v-database=cw100-start-datetime= "2019-04-17 08:00:00"-- stop-datetime= "2018-07-18 11:00:00" / usr/lib/mysql/mysql-bin.000154 > / root/binlog.txt

After reading the above about the technologies commonly used in MySQL, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.