Mysql 5.5 Master/Slave configuration steps, mysql

Mysql 5.5 Master/Slave configuration steps, mysql

The last time I introduced the mysql 5.1 master-slave configuration tutorial, this time we will implement mysql 5.5 master-slave replication, in fact, the configuration is basically the same, only a slight difference.
System: centos 5.x
Required software packages: mysql-5.5.33.tar.gz
Environment preparation:
Server a: 192.168.10.151 (master)
Server B: 192.168.10.152 (slave)

1. Preparations before installation
Wget http://mysql.llarian.net/Downloads/MySQL-5.5/mysql-5.5.33.tar.gz
Yum-y install gcc-c ++ libtool-libs autoconf freetype-devel gd libjpeg-devel
Libang-devel libxml2-devel ncurses-devel zlib-devel zip unzip curl-devel wget crontabs
File bison cmake patch mlocate flex diffutils automake make kernel-devel cpp
Readline-devel openssl-devel vim-minimal glibc-devel glib2-devel
Bzip2-devel e2fsprogs-devel libidn-devel gettext-devel expat-devel
Libcap-devel libtool-ltdl-devel pam-devel pcre-devel libmcrypt-devel

2. Install mysql on server
Tar zxf mysql-5.5.33.tar.gz & cd mysql-5.5.33
/Usr/sbin/groupadd mysql
/Usr/sbin/useradd-g mysql
Cmake-DCMAKE_INSTALL_PREFIX =/usr/local/mysql-packages =/tmp/mysql. sock-DDEFAULT_CHARSET = utf8-DDEFAULT_COLLATION = bytes-rows = complex-DWITH_READLINE = 1-rows = 1

Make
Make install

Chmod + w/usr/local/mysql
Chown-R mysql: mysql/usr/local/mysql
Cd support-files/
Cp my-medium.cnf/etc/my. cnf
Cp-f mysql. server/etc/rc. d/init. d/mysqld
Mkdir/var/lib/mysql
/Usr/local/mysql/scripts/mysql_install_db -- defaults-file =/etc/my. cnf -- basedir =/usr/local/mysql -- datadir =/var/lib/mysql -- user = mysql
Chmod + x/etc/rc. d/init. d/mysqld
Chkconfig -- add mysqld
Chkconfig mysqld on
Cat>/etc/ld. so. conf. d/mysql. conf <EOF
/Usr/local/mysql/lib/mysql
/Usr/local/lib
EOF
Ldconfig

If you are on a 64-bit machine, run the following command:
Ln-s/usr/local/mysql/lib/mysql/usr/lib64/mysql

If you are on a 32-bit machine, execute the following command:
Ln-s/usr/local/mysql/lib/mysql/usr/lib/mysql

Then execute:
Ln-s/usr/local/mysql/bin/mysql/usr/bin
Ln-s/usr/local/mysql/bin/mysqladmin/usr/bin
Ln-s/usr/local/mysql/bin/mysqldump/usr/bin
Service mysqld start
Mysqladmin-u root password 'Password'

Of course, if you think it is very troublesome to compile and install mysql, you can check out this article to install the mysql5.5 Binary Package.

The configuration for installing mysql on server B is the same as that for server.

3. Configure Master/Slave
On server:
Vi/etc/my. cnf
[Mysqld]
Log-bin = master-bin
Log-bin-index = master-bin.index
Server-id = 1
Innodb_file_per_table = 1
Binlog_format = mixed

Authorized copy users:
Mysql-u root-p
Grant replication slave on *. * to 'dbmysql' @ '%' identified by '123 ';
Flush privileges;

Restart mysql
Service mysqld restart

On server B:
Vi/etc/my. cnf
[Mysqld]
Relay-log = relay-log
Relay-log-index = relay-log.index
Server-id = 2
Innodb_file_per_table = 1
Binlog_format = mixed

Restart mysql
Service mysqld restart

Server B connects to the master server and copies
First, check the master Status on server.
Mysql> show master status;
+ ------------------- + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------- + ---------- + -------------- + ------------------ +
| Master-bin.000001 | 107 |
+ ------------------- + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)

Then connect to server B
Mysql> change master to master_host = '192. 168.10.151 ', master_user = 'dbmysql', maste
R_password = '000000', master_log_file = 'master-bin.000001 ', master_log_pos = 123456;

Check the slave status.
Mysql> show slave statusG
* *************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.10.151
Master_User: dbmysql
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: No
Slave_ SQL _Running: No
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: 107
Relay_Log_Space: 107
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL _ (www.111cn.net) CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_ SQL _Errno: 0
Last_ SQL _Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)

Start slave and check again
Mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

Mysql> show slave statusG
* *************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.10.151
Master_User: dbmysql
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Connecting
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: 107
Relay_Log_Space: 107
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master dbmysql @ 192.168.10.151: 3306 '-retry-time: 60 retries: 86400
Last_ SQL _Errno: 0
Last_ SQL _Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)

If the above error is returned, port 3306 is not opened in the firewall of the mysql master server and slave server. Go to server a and server B to open port 3306 and check the slave status of mysql.
Mysql> show slave statusG
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.151
Master_User: dbmysql
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: relay-log.000011
Relay_Log_Pos: 254
Relay_Master_Log_File: master-bin.000001
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: 107
Relay_Log_Space: 404
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_ SQL _Errno: 0
Last_ SQL _Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)

Okay, we can see that Slave_IO_Running and Slave_ SQL _Running are both yes, and there is no error prompt below.

4. Verify
Create a database on the mysql master
Mysql> create database emlog;
Query OK, 1 row affected (0.01 sec)

Check whether the database exists in mysql.
Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Emlog |
| Mysql |
| Performance_schema |
| Test |
+ -------------------- +
5 rows in set (0.02 sec)

We can see that the data has been synchronized and the mysql Master/Slave is successfully set up.
From: http://www.111cn.net/database/mysql/52862.htm

When configuring mysql master-slave Database Synchronization, must the master database name be the same as the slave database name?

Use replication-rewrite-db = dbmaster> dbslave
The version 5.0 is set so that the high version does not know whether it is supported
 
Mysql Master/Slave Database

After the master and slave nodes are configured, the master database inserts data into the binary log and sends it to the slave database. The slave database receives binary logs and writes them to the relay log. The slave database reads and inserts data from the relay log.