MySQL master-slave synchronization in a Linux environment--Add a new slave library

At present I think the database master and slave have two major application value:

1. The backup from the library is equivalent to the main library. Although the master-slave of the database can not replace/replace the backup, for example, the wrong data may destroy all the databases, but the master-slave is also in a readable state to maintain a backup of a way to implement.

2. From the library can alleviate the pressure of the main library, can improve performance. Since the library is read-only, in terms of reading the query, from the library can replace the main library, assume a certain amount of pressure, whether the pressure is from the user (application) or development, operation and maintenance of their own.

MySQL's master and slave is not difficult, and there are not many things to do, MySQL is the official document provided by the master and slave also made detailed instructions, explanations and explanations, but the total amount of space. Since many netizens have already written the relevant documents or articles in detail, this will not be mentioned here (you can also refer to the last part of this article "some commands and procedures used in the database master-slave configuration Process").

Simply say a few words about adding new from the library to the tips.

Because you need to know Master_log_file and Master_log_pos when adding from a library, you can see the current master_log_file and Master_log_ by performing a "show Master status" on the Master Master Library POS, but in fact this method can only be used in the case of the Master Master Library just started, that is, the database has just been built, no data is written to the case, or can be manually synchronized to the library from the first.

A convenient way is to export the database of the main library through mysqldump, and export events, triggers, functions, and views, as well as master-data, so that you can find master_log_ by looking at the first few lines of mysqldump exported files. File and Master_log_pos, when the SQL statement that will be exported from the main library is executed from the library, the Master_log_file and Master_log_pos can be configured to start the synchronization from the library.

A detailed description of the mysqldump and a backup script for the MySQL database can be found in the article "using the Linux shell script mysqldump backup MySQL database (detailed annotations)" and the comments in the article.

Export the database of the main library through mysqldump, and export events, triggers, functions, and views, as well as Master-data:

/usr/local/mysql/bin/mysqldump-uusername-hipaddress-ppassword-pport--routines--events--triggers-- Single-transaction--flush-logs--master-data=1–databases DatabaseName

If you want to export events, triggers, functions, and views separately without exporting data, you can do this:

/usr/local/mysql/bin/mysqldump-uusername-hipaddress-ppassword-pport--routines--events--triggers-- No-create-info--no-data--no-create-db

The

also displays some of the commands and steps used during a database master-slave configuration as a reference or memo:

# sat aug 22 11:23:03 cst 2015# get mysql 5.5.38 for  production use# https://downloads.mariadb.com/archive/index/p/mysql/v/5.5.38# https:// downloads.mariadb.com/archives/mysql-5.5/mysql-5.5.38.tar.gzwget -c https://downloads.mariadb.com/ archives/mysql-5.5/mysql-5.5.38-linux2.6-x86_64.tar.gz# remove some conflictsrpm -e  mysql-server mysqluserdel mysqlrm -rf /usr/local/mysql/rm -f /etc/my.cnf#   set user and groupgroupadd -r -g 27 mysql useradd -r - u 27 -g mysql mysql -c  "Mysql server"  -d /dev/null -s / sbin/nologingroupadd -r mysql useradd -r -g mysql mysql -c  "MySQL  server " -d /dev/null -s /sbin/nologin# install  mysql database  tar zxf mysql-5.5.38-linux2.6-x86_64.tar.gzcp -r mysql-5.5.38-linux2.6-x86_64 /usr/local/mysql# /usr/ Local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data  --user=mysql --explicit_defaults_for_timestamp --skip-name-resolve/usr/local/mysql/scripts/ mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql/data --user=mysql -- skip-name-resolve# run it temporarily # /usr/local/mysql/bin/mysqld_safe &/ Usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &# set mysql commands  ln -s /usr/local/mysql/bin/mysql /usr/bin/mysqlln -s /usr/local/mysql/bin/ Mysqladmin /usr/bin/mysqladminln -s /usr/local/mysql/bin/mysqldump /usr/bin/mysqldumpln  -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlogln -s /usr/local/mysql/bin/ Mysql_config /usr/bin/mysql_config# set mysql root password, etc/usr/local/mysql/bin/mysql_secure_installation#  set mysql library ln -s /usr/local/mysql/lib/libmysqlclient.so.18.0.0 /usr /lib64/libmysqlclient.so.18.0.0ln -s /usr/local/mysql/lib/libmysqlclient.so.18.0.0 /usr/lib64/ libmysqlclient.so.18ln -s /usr/local/mysql/lib/libmysqlclient.so.18.0.0 /usr/lib64/ libmysqlclient.sols -l /usr/lib64/libmysqlclient.so.18.0.0ls -l /usr/lib64/ Libmysqlclient.so.18ls -l /usr/lib64/libmysqlclient.sovim /etc/ld.so.conf.d/mysql-x86_64.conf      /usr/lib64/mysql     /usr/local/mysql/libldconfig#  set mysql replication # master dbcp /usr/local/mysql/support-files/ my-small.cnf /etc/my.cnfsed -i  "[email protected]\t= [email protected]        = [email prOtected] " /etc/my.cnfsed -i " [email protected]#[email protected][email protected ] " /etc/my.cnfsed -i " [email protected]#[email protected][email protected] "  /etc/my.cnf# slave dbcp /usr/local/mysql/support-files/my-small.cnf /etc/my.cnfsed  -i  "[email protected]\t= [email protected]        = [email protected] " /etc/my.cnf# read errors from logscat /usr/local/ mysql/data/' hostname '. err# some reference# server-id = 101# log-bin=/usr/local/ mysql/data/bin-log# max_binlog_size = 1500m# binlog_cache_size = 128k#  binlog-do-db = devdbops# binlog-ignore-db = mysql# log-slave-updates#  expire_logs_day=2# binlog_format=mixed# end mysql temporarilykill -term  ' PS  -ef | awk  '/mysqld_safe/ && ! /awk/ {print $2} '  | |  kill -KILL  ' ps -ef | awk  '/mysqld_safe/ && ! /awk/  {print $2} ' ps -ef | awk  '/mysqld_safe/ && ! /awk/  {print $2} ' kill -term  ' ps -ef | awk  '/mysqld/ && ! / awk/ {print $2} '  | |  kill -KILL  ' ps -ef | awk  '/mysqld/ && ! /awk/  {print $2} ' ps -ef | awk  '/mysqld/ && ! /awk/ {print  $2} ' # setting sysvinit cp /usr/local/mysql/support-files/mysql.server /etc/ init.d/mysqlchmod +x /etc/init.d/mysql# start mysql databaseservice mysql  startservice mysql status# some operation about replicaion# master  dbgrant all  Privileges on *.* to [email protected] "%"  IDENTIFIED BY  "root"; flush privileges; create user  ' dev ' @ '% '  IDENTIFIED BY  ' dev '; create database if not exists devdbops; grant alter,alter routine,create,create routine,create temporary tables,create  view,delete,drop,execute,index,insert,lock tables,select,update,show view on devdbops.*  TO  ' dev ' @ "%"; use devdbops; create table  ' testtable '   (' id '   int not null , ' name '    varchar (255)  null , ' value '   varchar (255)  character set utf8 collate  utf8_general_ci NULL ,PRIMARY KEY  (' id '));insert into  ' testtable '   (' Id ',  ' name ',  ' value ')  VALUES  (' 0 ',  ' a ',  ' B ');# create user  ' Repl ' @ '%.mydomain.com '  IDENTIFIED BY  ' SlavepaSS ';# grant replication slave on *.* to  ' repl ' @ '%.mydomain.com ';#  create user  ' repl ' @ '% '  IDENTIFIED BY  ' Slavepass ';# grant replication  slave on devdbops.* to  ' replication ' @ '% '; show master status; show processlist \g; show slave hosts; quit;# slave dbstop slave; Change master to master_host= ' 192.168.1.101 ', master_user= ' root ', master_password= ' root ', master_ Log_file= ' mysql-bin.000002 ', master_log_pos=107; start slave; Show slave status \g;# read for test

Tag:mysql master-slave configuration, MySQL add from library, MySQL backup view function, MySQL backup script, linux install MySQL

--end--

This article is from "Communication, My Favorites" blog, please make sure to keep this source http://dgd2010.blog.51cto.com/1539422/1689171

MySQL master-slave synchronization in a Linux environment--Add a new slave library