Mysql master-slave replication, implemented on a single server

Original article :? Blog. csdn. netsongxixiarticledetails8737555 now the company is copying data from a server. Now we know that you need to install multiple mysql services. Now, under the existing single-instance database, different ports are divided into the red section below; [root @ mysql ~] # Netstat-t

Original article :? Http://blog.csdn.net/songxixi/article/details/8737555 is now the company to the master on a server from the replication, now understand that the need to install multiple mysql different services, can be, I now in the existing single instance database, different ports are divided into the red section below; [root @ mysql ~] # Netstat-t

Original article :? Http://blog.csdn.net/songxixi/article/details/8737555

Now, the company replicates data from a single server. Now we know that you need to install multiple mysql services. Now, under the existing single-instance database, different ports are divided into the red section below; [root @ mysql ~] # Netstat-tunlpActive Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0: 1001 0.0.0.0: * LISTEN 2515/rpc. statd tcp 0 0 0.0.0.0: 111 0.0.0.0: * LISTEN 2483/portmap tcp 0 0 127.0.0.1: 631 0.0.0.0: * LISTEN 2750/cupsd tcp 0 0 127.0.0.1: 25 0.0.0.0: * LISTEN 3056/sendmail: acce tcp 0: 3306: * LISTEN 4025/mysqld tcp 0: 3307 ::: * LISTEN 17423/mysqld tcp 0: 3308: * LISTEN 17388/mysqld tcp 0: 3309: * LISTEN 20371/mysqld tcp 0 ::: 22: * LISTEN 2741/sshd udp 0 0 0.0.0.0: 995 0.0.0.0: * 2515/rpc. statd udp 0 0 0.0.0.0: 998 0.0.0.0: * 2515/rpc. statd udp 0 0 0.0.0.0: 42601 0.0.0.0: * 3204/avahi-daemon: udp 0 0 0.0.0.0: 5353 0.0.0.0: * 3204/avahi-daemon: udp 0 0 0.0.0.0: 111 0.0.0.0: * 2483/portmap udp 0 0 0.0.0.0: 631 0.0.0. 0: * 2750/cupsd udp 0 0: 5353: * 3204/avahi-daemon: udp 0 0: 59254: * 3204/avahi-daemon: [root @ mysql] # mysqld_multi -- defaults-extra-file =/etc/mysqld_multi.cnf reportReporting MySQL serversMySQL server from group: mysqld2 is runningMySQL server from group: mysqld3 is runningMySQL server from group: mysqld4 is running [root @ mysql] # lsof-I: 3308 COMMAND PID USER FD TYPE DEVICE SIZE NODE NAMEmysqld 17388 mysql 11u IPv6 45429 TCP *: tns-server (LISTEN) [root @ mysql] # lsof-I: 3307 command pid user fd type device size node NAMEmysqld 17423 mysql 11u IPv6 45479 TCP * psession-prxy (LISTEN) [root @ mysql] # lsof-I: 3309 command pid user fd type device size node NAMEmysqld 20371 mysql 11u IPv6 47851 TCP *: tns-adv (LISTEN) [root @ mysql] # lsof-I: 3306 COMMAND PID USER FD TYPE DE Vice size node NAMEmysqld 4025 mysql 11u IPv6 16575 TCP *: mysql (LISTEN) [root @ mysql] # [root @ mysql ~] # When the above services are started normally, configure the master and slave nodes. I have not adjusted the IO thread. I do not know whether the master node is in the slave node replication relationship for different ports, the following is the last error I encountered in configuration. Please help me find it and make a brick... Mysql> show slave status \ G; ***************************** 1. row ************************* Slave_IO_State: Master_Host: 192.168.1.10 Master_User: slave001 Master_Port: 3308 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 1027 Relay_Log_File: localhost3308-relay-bin.000001 Relay_Log_Pos: 4 rows: mysql-bin.000003 rows: No rows: Yes rows: Last_Errno: 0 Last_Error: Skip_Counter: 0 condition: 1027 Relay_Log_Space: 107 Until_Condition: None Until_Log_File: Unknown: 0 condition: No condition: Unknown: Master_SSL_Cert: Unknown: Master_SSL_Key: Unknown: No condition: 1593 Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the -- replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it ). last_ SQL _Errno: 0 Last_ SQL _Error: Usage: Master_Server_Id: 11 row in set (0.00 sec) ERROR: No query specifiedmysql> I added the following red content to mysqld_multi.cnf. The copy content is started or an ERROR is returned, please advise Wow [mysqld_multi] mysqld =/usr/local/mysql/bin/mysqld_safemysqladmin =/usr/local/mysql/bin/mysqladminuser = multi_adminpassword = my_password [mysqld2] socket =/tmp /mysql3307.sockport = 3307pid-file =/usr/local/mysql/data3307/localhost3307.piddatadir =/usr/local/mysql/data3307 # language =/usr/local/mysql/share/mysql/englishuser = mysql [mysqld3] # mysqld =/path/to/mysqld_safe # ledir =/path/to/mysqld-binary/# mysqladmin =/path/to/mysqladminsocket =/tmp/mysql3308.sockport = 3308pid-file =/usr/local/mysql/data3308/localhost3308.piddatadir =/usr/local/mysql/data3308 # language =/usr/local/mysql/share/mysql/swedishuser = mysqlmaster-host = 192.168.1.10master-user = slave001master-password = slave001master-port = 3306replicate-do-db = test error: mysql> show slave status \ G; ***************************** 1. row ************************* Slave_IO_State: Master_Host: 192.168.1.10 Master_User: slave001 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000018 Read_Master_Log_Pos: 107 Relay_Log_File: localhost3308-relay-bin.000001 Relay_Log_Pos: 4 rows: mysql-bin.000018 rows: No rows: Yes rows: Last_Errno: 0 Last_Error: Skip_Counter: 0 condition: 107 Relay_Log_Space: 107 Until_Condition: None Until_Log_File: Unknown: 0 condition: No condition: Unknown: Master_SSL_Cert: Unknown: Master_SSL_Key: Unknown: No condition: 1593 Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the -- replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it ). last_ SQL _Errno: 0 Last_ SQL _Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 11 row in set (0.00 sec)

Ah, after several hours of thinking, I finally saw the true face of Lushan! The following figure shows the effect of successful startup. [root @ mysql ~] # Mysqld_multi -- defaults-extra-file =/etc/my. cnf reportReporting MySQL serversMySQL server from group: mysqld1 is runningMySQL server from group: mysqld2 is runningMySQL server from group: mysqld3 is runningMySQL server from group: mysqld4 is running [root @ mysql ~] # Mysqld_multi -- defaults-extra-file =/etc/my. cnf reportReporting MySQL serversMySQL server from group: mysqld1 is runningMySQL server from group: mysqld2 is runningMySQL server from group: mysqld3 is runningMySQL server from group: mysqld4 is running [root @ mysql ~] # Mysqld_multi -- defaults-extra-file =/etc/my. cnf reportReporting MySQL serversMySQL server from group: mysqld1 is runningMySQL server from group: mysqld2 is runningMySQL server from group: mysqld3 is runningMySQL server from group: mysqld4 is running [root @ mysql ~] # Mysqld_multi -- defaults-extra-file =/etc/my. cnf reportReporting MySQL serversMySQL server from group: mysqld1 is runningMySQL server from group: mysqld2 is runningMySQL server from group: mysqld3 is runningMySQL server from group: mysqld4 is running [root @ mysql ~] # In fact, some parameters are not properly configured when I configure muti, and the server-id service cannot be started at that time, I put all mysqld [1-4] of muti in our configuration file/etc/my. cnf, including our 3306 port instance library, can be run together at startup! My. for more information about cnf configuration files, see; # The following options will be passed to all MySQL clients [mysqld_multi] mysqld =/usr/local/mysql/bin/mysqld_safemysqladmin =/usr/local/mysql/bin/mysqladminuser = root # here follows entries for some specific programs # The MySQL server [mysqld1] datadir =/usr/local/mysql/datasocket =/tmp/mysql. sockuser = mysqlold_passwords = 1 port = bytes = 1log-bin = mysql-binmax_connections = 1000key_buffer_size = bytes = 134228table_cache = 10000tmp_table_size = 5362log-slow-queries = slow. loglong_query_time = 1concurrent_insert = 2thread_cache_size = 300 # log =/usr/local/mysql/data/mysql1.logpid-file =/usr/local/mysql/data/mysql1.pidlog-error =/usr/local /mysql/data/log.1log-slave-updatesslave-skip-errors = ALL [mysqld2] datadir =/usr/local/mysql/data3307socket =/tmp/mysql3307.sockuser = login = 222log-bin = mysql-binmax_connections = 1000key_buffer_size = 218348query_cache_type = 0read_rnd_buffer_size = 1342128table_cache = rjtmp_table_size = 536612log-slow-queries = slow. loglong_query_time = 1concurrent_insert = rows = 300 port = 3307pid-file =/usr/local/mysql/data3307/response-error =/usr/local/mysql/data3307/log.2 [mysqld3] datadir =/ usr/local/mysql/data3308socket =/tmp/mysql3308.sockuser = bytes = 223log-bin = mysql-binmax_connections = 1000key_buffer_size = bytes = 1342128table_cache = bytes tmp_table_size = bytes = slow. loglong_query_time = 1concurrent_insert = runtime = 300 port = 3308pid-file =/usr/local/mysql/data3308/metrics-error =/usr/local/mysql/data3308/log.3 [mysqld4] datadir =/ usr/local/mysql/data3309socket =/tmp/mysql3309.sockuser = users = 224log-bin = mysql-binmax_connections = 1000key_buffer_size = bytes = 1342128table_cache = bytes tmp_table_size = bytes = slow. loglong_query_time = 1concurrent_insert = login = 300 port = 3309pid-file =/usr/local/mysql/data3309/localhost3309.pidlog-error =/usr/local/mysql/data3309/log.4 or above is the configured start content, I want to allocate the server-id to each mysqld and keep it unique to implement the basic configuration Requirements for the replication of a single server; the steps for configuring the master and slave nodes are simple. If you do not know how to configure the master node, you can leave a message at any time. The following figure shows the effect of the master and slave startup parameters after configuration is successful; mysql> show slave status \ G; ***************************** 1. row ************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.10 Master_User: backup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000032 Read_Master_Log_Pos: 893 Relay_Log_File: localhost3307-relay-bin.000003 Relay_Log_Pos: 253 rows: mysql-bin.000032 rows: Yes rows: Keys: keys: failed: counters: 0 Last_Error: Skip_Counter: 0 condition: 893 Relay_Log_Space: 1194 Until_Condition: None Until_Log_File: Failed: 0 condition: No condition: Master_SSL_Cert: Keys: Master_SSL_Key: seconds_Behind_Master: Role: No Last_IO_Errno: 0 Last_IO_Error: Last_ SQL _Errno: 0 Last_ SQL _Error: Role: Master_Server_Id: 11 row in set (0.00 sec) ERROR: No specquery ifiedmysql

Original article address: Mysql master-slave replication, which is implemented on a single server. Thank you for sharing it with the original author.