About MySQL 5.7 enabling bin log master-slave replication

Posted by croix on Tue, 31 Dec 2019 18:38:36 +0100

Master-slave replication: generally used for real-time backup.

The next steps are as follows: master master database configuration synchronization, slave database configuration synchronization, master lock table / backup, slave data recovery, slave synchronization enabled, master unlocking, and completion.

##################### mysql5.7 bin-log Log open, Must set server-id ###################

###############  master host
vim /etc/my.cnf  # Add the following configuration to [mysqld]
server-id=1 #Server id (master and slave must be different)
log-bin=mysql-bin # Open the log (the host needs to open it). This MySQL bin can be customized or added with the path
binlog-do-db=monitor_db #Library to synchronize with slave
binlog-ignore-db=mysql #Libraries that do not synchronize with slaves (write multiple lines)
binlog-ignore-db=mysqlslap
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
expire_logs_days=3 # Automatically clean up the log files 3 days ago, which can be modified as needed


#Save exit, restart mysql service




 




cd mysql/var                      # Can see mysql-bin.000001



show variables like "%log_bin%";  # See log_bin  Whether ON

-- To grant authorization backup Users can make remote replication
-- grant FILE on *.* to 'backup'@'%' identified by 'backup';
-- grant replication slave on *.* to 'backup'@'%' identified by '123456';
-- flush privileges;
-- select user,authentication_string,host from user;

-- flush tables with read lock;  -- Lock primary database, Prevent master-slave inconsistency (at this time, the master database can no longer write data)
-- show master status\G   -- display master Write down the informationFileandPosition






#Then perform a backup of the primary database and restore to the secondary database. (the main database cannot be served continuously)




 




###############  slave Slave
vim /etc/my.cnf  # Add the following configuration to [mysqld]




log-bin=mysql-bin
server-id=2
binlog-ignore-db=information_schema
binlog-ignore-db=cluster
binlog-ignore-db=mysql
replicate-do-db=monitor_db
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60 






#Save exit and restart mysql and SQL to do the following:
-- stop slave;
-- change master to master_host='192.168.112.6',master_user='bakcup',master_password='123456',master_log_file='mysql-bin.000001', master_log_pos=154;
-- start slave;
--UNLOCK TABLES; -- execute unlocking the master database on the master database
 --show slave status\G -- show slave information
 #Master log file is the File displayed by the Master host show master status, while master log POS is the Position displayed.
#Then, you can show the slave status to see whether the position and file in the slave are consistent with those in the master.

#CURD the master database and verify that the slave database is synchronized accordingly




 




################################ Other common operations ############################
flush logs;           -- Refresh log to generate new mysql-bin file
show master status;   -- View binary logs and Position
 
insert into t1 values(1);
insert into t1 values(2);

reset master; --empty bin-log
\s    --View character set ddddd

#Use your own tools to view bin-log
mysql/bin/mysqlbinlog --no-defaults mysql-bin.000001 |more

#When backing up the database, you can flush logs; It will generate new bin-log
#There is no backup data in the recovery time difference to the test database
mysql/bin/mysqlbinlog --no-defaults mysql-bin.000002 |/usr/local/mysql/bin/mysql -uroot -p123 test


#Backup data: -F Namely flush logs; -l It's a read lock., Can only read but not write.
mysqldump -uroot -p123 test -l -F > '/tmp/test.sql'

#Restore data: first restore the previous backup, and then restore within the time difference
mysql -uroot -p123 test < /tmp/test.sql  -- parameter-f Yes error encountered skip
mysql/bin/mysqlbinlog --no-defaults mysql-bin.000002 |/usr/local/mysql/bin/mysql -uroot -p123 test

#The recovery of selected nodes means the selection of multiple starting points
mysql/bin/mysqlbinlog --no-defaults mysql-bin.000002 --start-position="193" --stop-position="280" |/usr/local/mysql/bin/mysql -uroot -p123 test


show slave status\G # Reverse row and column view. The following two columns indicate that master-slave synchronization is successful 
Slave_IO_Running: Yes
Slave_SQL_Running:Yes 



######################### Built in pressure test ################################# 
# Execute the command under the OS shell: 
mysqlslap -hlocalhost -uroot -p123456 -P3306 --concurrency=5000 --iterations=1 --auto-generate-sql --auto-generate-sql-load-type=mixed --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=5000
# May need to be modified first OS Connection number ulimit -n 65535 
# And mysql's max connect  
show variables like '%max_connections%'  -- maximum connection
set global max_connections=6000;         -- Change settings in the current process. Permanent settings need to be my.cnf Join in


-- SELECT @@innodb_buffer_pool_size;     -- See innodb_buffer_pool_size 
-- SELECT @@innodb_buffer_pool_chunk_size; -- Block size,Default 128 M
-- SELECT @@innodb_buffer_pool_instances;  -- Instance number, Default 8
-- SET GLOBAL innodb_buffer_pool_size=1073741824;  -- 1G
-- SET GLOBAL innodb_buffer_pool_size=8589934592;  -- 8G -- Set up innodb_buffer_pool_size No need to restart.



    
        
Topics:
            MySQL
            Database
            SQL
            mysqlbinlog
        

            

        
Hot Topics



 

        

    



    ©2024 Programmer Think