MySQL master-slave replication (1), mysql master-slave Replication

MySQL master-slave replication (1), mysql master-slave Replication

<Span style = "font-size: 18px;"> mysql replication has the following advantages: (generally, "add, delete, and modify" is handed over to the primary database, "query" depends on the database.) 1. If a problem occurs on the master server, you can quickly switch to the service provided by the slave server. 2. You can perform the query operation on the slave server, reduce the access pressure on the master server 3: Backup can be performed on the slave server to avoid affecting the services of the master server during the backup-Note: generally, only infrequently updated data or data with high real-time requirements can be queried from the server. The data with high real-time performance still needs to be obtained from the master database. 1: configuration steps of the master server: 1: log on to mysql database mysql-uroot-p1234133: grant all on * to the slave server *. * to user1@192.168.10.2 identified by "pass123"; (all indicates that all permissions mean authorizing all users, all tables about the database (only this server 192.168.10.2, if the host is a string enclosed by single quotes 'leyangjunpc'): the account is user1 and the password is pass123) </span> <span style = "font-size: 18px; "> * grant replication slave, replication client on *. * TO repl @ '2017. 168.0.% 'identified BY 'p4ssword'; </span> <span style = "font-size: 18px;"> (a permission used BY the replication Master/Slave server to copy code) 3: modify the configuration file my. cnf/ini, enable bin-log, and set the server-id value log-bin = mysql-binserver-id = 1 (the master-slave server-id must be unique, cannot be the same)-the same is true for one master node and multiple slaves. 4. Set read locks on the master server to ensure that no database operation is performed to obtain a consistent snapshot, in this way, you can back up the data.) flush tables with read lock; 5: view the current binary log name and offset value on the master server. show master status; 6: The master database server has stopped updating, generate a backup for the primary database. There are two backup methods: one is cp full data 2: mysqldump backup data method (recommended) if the primary database service can be stopped, so the direct cp data file should be the fastest way to generate a snapshot (that is, the cp data file is executed from the database tar-cvf data.tar data (the entire copy) 7: after the master database is backed up, the primary database can restore write operations. The remaining operations only need to be performed on the slave server: unlock tables; 8: Restore the consistent backup of the primary database to the slave database, extract the above compressed package and put it in the corresponding directory. 2. configuration steps from the server:-> scenario (copy the database first, can Play synchronization later-master data changes are synchronized immediately) MASTER: mysql/bin/mysqldump-uroot-p test-l-F> "/tmp/test. SQL "; master-backup file master: copy the sub-files to the slave Database: linux Command: scp/tmp/test. SQL 192.168.10.2:/tmp/master/tmp/test. copy the SQL statement to the slave database-tunnel transmission from: Reset master; clear the log from: recover the data that has just been copied to test. sqlmysql/bin/mysql-uroot-p123 test </tmp/test. check whether the connection master server is mysql/bin/mysql-uuser1-p123455 test-h192.168.10.11: Modify the server-id of the database in the configuration file, note that the server-id value must be unique and cannot be the same as the master data configuration. If there are multiple slave servers, each slave server must have its own unique server-id value to enable the bin-log2: In the configuration file-> enable and configure: (use the following if an error is reported) master-host = 192.168.10.1 (master database IP) master-user = user1 (user Name authorized for configuration) master-password = 123456 (password authorized for configuration) master-port = 3306 (port used to link the master server) -- restart MySQL Server * restart ERROR Handling Method *: ERROR: 120401 15:45:44 [ERROR] C: \ Program Files \ MySQL Server 5.5 \ bin \ mysqld: unknown variable 'master-host = 192.168.8.111 '120401 15:45:44 [ERROR] Aborting * 10 million note ------ solution ------------------ Mysql version does not support parameters similar to "master-host" since 5.1.7 run the following command on the database; change master to master_host = 'masterip', master_user = 'slave ', master_password = 'slvaepass'; slave start; restart 3: after configuration, check whether the method is synchronized with the master server (if the data synchronization fails, check *): show slave status \ G to see: * Salve_IO_Running: yes (it indicates that the bin-log of the master server is successfully retrieved and synchronized. I will synchronize one line when writing a line) * Salve_ SQL _Running: yes (the bin-log is obtained and the SQL statement is successfully executed. The master data is consistent.) -- If yes is displayed, the master server has synchronized master_Log_File: mysql-bin.00002 (synchronous bin-log) Read_Master_Log_Pos: 106 (location, show master status master to see it) Connect_Retry: 60 (back to the master server every 60 seconds to synchronize the bin-log) trace log: Relay-Log_File: localhost-relay-bin.00004 (the log file generated from the server, not the mysql-bin.xxx) after the data is synced, we can query data from the server without going to the master server, reducing load 4: starting the replication thread from the common database commands (1) start slave (getting data from the master server) (2) stop salve stop replication thread (3) show slave status view database status-link status information with the master server (4) show master logs view master database bin-log (5) change master to dynamically change the configuration of the master server (6) show processlist view database running processes 5: mysql common error ********** synchronization from the database ******** show slave status shows that slave_ SQL _runing is NO, seconds_behind_master is null cause:: the program may perform write operations on slave. B: It may be caused by transaction rollback after the slave machine is restarted. 1: slave stop; set GLOBAL SQL _SLAVE_SKIP_COUNTER = 1; set a global parameter slave start; solution 2: (1): salve (slave) database, slave stop; -- stop the master database of the slave service and show master status; get the & A log file name & B location -- get the current binary log and offset (2) salve database on the master server: view the status, if yes, perform manual synchronization on the salve server. Manually change master to master_host = "192.168.101.1", master_uset = "user1", master_password = "pass123456", master_port = 3306, master_log_file = "mysql-bin.00003", step (1) found & A log file name master_log_pos = 98; step (1) found & B location -- can be manually synchronized after (3) start salve start. (4) Check Salve_ SQL _Runing as yes by using show salve status. If Seconds_Behind_Master is 0, it is normal </span>

Mysql master-slave Replication

-- The host opens two windows, one for mysql and the other for shell.
-- Host blocking write operations
Mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
Mysql> show master status;

+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000003 | 983407 | openser | mysql, test |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)
-- Export the host database in another window
Mysqldump-u root-ppassword -- master-data = 2 -- opt-R openser> openser20120204. SQL
-- Unlock the window host just now
Mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
Mysql>
-- Package data files to the slave machine
Drop database openser;
Create database openser;
Mysql-u root-ppassword openser <openser20120204. SQL
-- Slave operation
Slave stop;
Reset slave;
Change master to MASTER_HOST = '192. 168.21.26 ',
MASTER_USER = 'repl _ user ',
MASTER_PASSWORD = 'Password ',
MASTER_LOG_FILE = 'mysql-bin.000003 ',
MASTER_LOG_POS = 983407;
Start slave;
Show slave status \ G

Q: 1. What is the concept of mysql master-slave replication? In some cases, it is best to give an example;

1. Master-slave replication is used to build a database environment exactly the same as that of the master database, called a slave database. The master database is generally a real-time business database, the functions and usage of databases generally include:
First, as a backup database, after the master database server fails, you can switch to the slave database to continue working;
Second, backup and data statistics can be performed from the database, without affecting the performance of the primary database;
2. read/write splitting refers to the use of different databases for reading and writing, of course, generally on different servers. The read/write environment on the same server is only used for testing.
In general, the configuration of the read/write database environment is: one written database, one or more read databases, each database is located on a different server, making full use of the server performance and database performance; of course, it involves how to ensure data consistency between read and write databases, which can be achieved through master-slave replication technology.
The general application scenario is: the service throughput is large, and the load for reading databases (which can be simply understood as the proportion and impact of select statements) is large;
The official mysql-proxy is a software that implements multiple functions such as read/write splitting and Server Load balancer.