MySQL Master-slave configuration

1. Install and configure MySQL

2. Start the MySQL service and synchronize data from the master/slave server [[Email protected] ~]# /etc/init.d/mysqld startstarting mysql  success! [[email protected] ~]# /etc/init.d/mysqld startstarting mysql  success! [[email protected] ~]# rsync -avlp [email protected]:/data/ mysql/ /data/mysql/   //synchronizes data on the primary server with data from the server 3. Configure master (master) [[email protected] ~]#  vi /etc/my.cnf   //Modify or add server-id=1log-bin=mysql-bin  you can also add the following parameters: binlog-do-db= db1,db2  #需要同步的库binlog-ignore-db=db1,db2  #忽略不同步的库  [[email protected] ~]# /etc/ init.d/mysqld restart   //After modifying the configuration file, restart service shutting down mysql.  success! starting mysql.  success! [[email protected] ~]# mysql -uroot   // This machine's MySQL has no password mysql> grant replication slave on *.* to  ' repl ' @ ' 192.168.1.119 '  identified by  ' 123456 ';   //repl is the user who accesses the master-side MySQL data for the slave end, with a password of 123456;192.168.1.119 for slave IP. query ok, 0 rows affected  (0.06 sec) mysql> flush privileges;    //refreshes the library, the memory data is written to disk query ok, 0 rows affected  (0.00 SEC) mysql>  flush tables with read lock;   //locks the database, and no data changes are allowed at this time query ok, 0  rows affected  (0.00 sec) mysql> show master status;+------------------+- ---------+--------------+------------------+| file              | position | binlog_do_db | binlog_ignore_db |+--------- ---------+----------+--------------+------------------+| mysql-bin.000032 |       106 |              |  mysql            |+------------------+----------+--------------+------ ------------+1 row in set  (0.00 SEC) 4. Set from (slave) [[email protected] ~]#  vi /etc/my.cnfserver-id = 2  #这个数值不能和主一样replicate-do-db=db1,db2  and   replicate-ignore-db=db1,db2  #意义同主的那两个可选参数, if the Lord has been configured then it is not necessary to configure [[EMAIL PROTECTED] ~]# /ETC/INIT.D /mysqld restart   //Restart Service shutting down mysql. success! starting  mysql. success! [[email protected] ~]# mysql -urootmysql> slave  Stop query ok, 0 rows affected, 1 warning  (0.00 sec) Mysql> change  master to master_host= ' 192.168.1.111 ',  master_port=3306, master_user= ' Repl ',  Master_password= ' 123456 ',  master_log_file= ' mysql-bin.000032 ',  master_log_pos=106;//master_host: The IP of the primary server. Master_user: User name established when configuring the primary server。 Master_password: User password. Master_port: The primary server MySQL port, if the default port can be omitted. Master_log_file and Master_log_pos must be in agreement with the Lord. query ok, 0 rows affected  (0.05 sec) mysql> slave start; query ok, 0 rows affected  (0.01 sec) mysql> show slave status\g ;*************************** 1. row ***************************                slave_io_state: waiting for master  to send event                   Master_Host: 192.168.1.111                   Master_User: repl                   master_port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000032           Read_Master_Log_Pos: 106                Relay_Log_File: localhost-relay-bin.000002                 Relay_Log_Pos: 251         Relay_Master_Log_File: mysql-bin.000032              slave_io_running: yes   //yes indicates a successful configuration             Slave_SQL_Running: Yes   //yes indicates a successful configuration               replicate _do_db:          replicate_ignore_db:            Replicate_Do_Table:       Replicate_Ignore_Table:       Replicate_Wild_Do_Table:  Replicate_Wild_Ignore_Table:                    last_ errno: 0                    Last_Error:                  skip_counter: 0          exec_ master_log_pos: 106               relay_log_space: 410               until_condition: none               until_log_file:                 Until_Log_Pos:  0           master_ssl_allowed: no            Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                master_ssl_key:        seconds_behind_master: 0master_ssl_ verify_server_cert: no                 last_io_errno: 0                last_io _error:               last_sql_ Errno: 0               last_ sql_error:1 row in set  (0.00 sec) error:no query specified5. Test Master-Slave:mysql>  unlock tables;  //The Lord database is locked, this is unlocked query ok, 0 rows affected  (0.00  SEC) mysql> show databases;+--------------------+| database            |+--------------------+| information_schema | |  aming              | |  mysql              | |  test                |+--------------------+4 rows in set  (0.05 sec) mysql> create database cangls; query ok, 1 row affected  (0.00 sec) mysql> use cangls;database  Changed mysql> select database (); +------------+| database ()  |+------------+|  cangls     |+------------+1 row in set  (0.00 sec) mysql>  show tables; empty set  (0.00 sec) mysql> create table  ' Pre_forum_post '   (     ->    ' pid '  int (Ten)  unsigned NOT NULL,     ->    ' FID '  mediumint (8)  unsigned NOT NULL DEFAULT  ' 0 ',     ->    ' Tid '  mediumint (8)  unsigned NOT NULL  default  ' 0 ',     ->    ' First '  tinyint (1)  NOT NULL DEFAULT  ' 0 ',    ->     ' Author '  varchar (All)  NOT NULL DEFAULT  ',    ->     ' Authorid '  mediumint (8)  unsigned NOT NULL DEFAULT  ' 0 ',     ->    ' subject '  varchar  NOT NULL DEFAULT  ',     ->    ' Dateline '  int (Ten)  unsigned not null default   ' 0 ',    ->    ' message '  mediumtext NOT NULL,     ->    ' Useip '  varchar (+)  NOT NULL DEFAULT  ',     ->    ' Port '  smallint (6)  unsigned NOT NULL  default  ' 0 ',    ->    ' invisible '  tinyint (1)  not null  DEFAULT  ' 0 ', &NBsp;   ->    ' Anonymous '  tinyint (1)  NOT NULL DEFAULT  ' 0 ',    ->    ' usesig '  tinyint (1)  not null default   ' 0 ',    ->    ' Htmlon '  tinyint (1)  NOT NULL  default  ' 0 ',    ->    ' bbcodeoff '  tinyint (1)  NOT NULL  DEFAULT  ' 0 ',    ->    ' smileyoff '  tinyint (1)  not  NULL DEFAULT  ' 0 ',    ->    ' parseurloff '  tinyint (1)  NOT NULL DEFAULT  ' 0 ',    ->    ' attachment '   tinyint (1)  NOT NULL DEFAULT  ' 0 ',    ->    ' rate '  smallint (6)  NOT NULL DEFAULT  ' 0 ',    ->    ' Ratetimes '  tinyint (3)  unsigned not null default  ' 0 ',    ->    ' status '   Int (Ten)  NOT NULL DEFAULT  ' 0 ',    ->    ' tags '   varchar (255)  NOT NULL DEFAULT  ' 0 ',    ->    ' comment '  tinyint (1)  NOT NULL DEFAULT  ' 0 ',    ->    ' Replycredit '  int (Ten)  NOT NULL DEFAULT  ' 0 ',    ->     ' position '  int (8)  unsigned not null auto_increment,    - >   PRIMARY KEY  (' tid ', ' position '),    ->    unique key  ' pid '   (' pid '),    ->   key  ' FID '   (' FID '),    ->   key  ' Authorid '   (' Authorid ', ' invisible '),     ->   key  ' Dateline '   (' Dateline '),     ->   key   ' invisible '   (' invisible '),    ->   key  ' Displayorder '   (' tid ', ' invisible ', ' Dateline '),    ->   key  ' first '   (' Tid ', ' first ')     -> )  ENGINE=MyISAM DEFAULT CHARSET=gbk     -> ; query ok, 0 rows affected  (0.04 sec) mysql> show tables;+----------- -------+| tables_in_cangls |+------------------+| pre_forum_post   |+----------- -------+1 row in set  (0.01 sec) mysql> show master status;+--------- ---------+----------+--------------+------------------+| file              | position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+| mysql-bin.000032 |      1808 |               | mysql            |+------------ ------+----------+--------------+------------------+1 row in set  (0.00 sec) from service: MySQL > show databases;+--------------------+| database            |+--------------------+| information_schema | |  aming              | |  cangls             | |  mysql              | |  test               |+--------------------+5 rows in set  (0.04 sec) mysql> use cangls;d atabase changedmysql> show tables;+------------------+| tables_in_cangls |+------- -----------+| pre_forum_post   |+------------------+1 row in set  (0.00  SEC) mysql> show slave status\g;*************************** 1. row *****                 Slave_IO_State: Waiting for master to send event                   Master_Host:  192.168.1.111                   Master_User: repl                   master_port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000032           Read_Master_Log_Pos: 1808                Relay_Log_File: localhost-relay-bin.000002                 Relay_Log_Pos: 1953         Relay_Master_Log_File: mysql-bin.000032              Slave_IO_Running: Yes             Slave_SQL_Running: Yes               replicate_do_db:          replicate_ignore_db:            Replicate_Do_Table:        Replicate_ignore_table:      replicate_wild_do_table:  replicate_wild_ ignore_table:                    Last_Errno: 0                    Last_Error:                  Skip_Counter: 0           Exec_Master_Log_Pos: 1808               Relay_Log_Space: 2112              &nBsp until_condition: none                Until_Log_File:                 until_log_pos: 0           master_ssl_ allowed: no           master_ssl_ca_file:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                master_ssl_key:        seconds_ behind_master: 0master_ssl_verify_server_cert: no                 last_io_errno: 0                 Last_IO_Error:                Last_SQL_Errno: 0                Last_SQL_Error:1 row in set  (0.01 sec) error:no  Query specified

MySQL Master-slave configuration