MySQL 5.5 database master configuration steps detailed

The last time we introduced the MySQL 5.1 master configuration tutorial, this time we come to achieve the MySQL 5.5 Master-slave replication, in fact, the overall configuration is almost the same, only a few subtle differences.
   System: CentOS 5.x
   Required Packages: mysql-5.5.33.tar.gz
   Environment preparation:
         Server a:192.168.10.151 (primary)
        Server B : 192.168.10.152 (from)
 
1. Prepare
wget http://mysql.llarian.net/Downloads/MySQL-5.5/before installation mysql-5.5.33.tar.gz
yum-y Install gcc  gcc-c++ libtool-libs autoconf freetype-devel gd libjpeg-devel
Libpng -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 A
Tar zxf mysql-5.5.33.tar.gz && CD mysql-5.5.33
/usr/sbin/groupadd MySQL
/usr/sbin/useradd-g MySQL MySQL
Cmake-dcmake_install_prefix=/usr/local/mysql-dmysql_unix_addr=/tmp/mysql.sock-ddefault_charset=utf8-ddefault_ Collation=utf8_general_ci-dwith_extra_charsets=complex-dwith_readline=1-denabled_local_infile=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, then you execute 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

And 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 feel that compiling and installing MySQL is cumbersome, then you can go and see this article to install the mysql5.5 binary package.

Server B installs the MySQL configuration as Server A, which is not mentioned here.

3. Configuration Master
On Server A:
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

To authorize replication users:
Mysql-u root-p
Grant replication slave on *.* to ' dbmysql ' @ '% ' identified by ' 123456 ';
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

and restart MySQL.
Service mysqld Restart

Server B connects to the master server and replicates
View the status of master on Server a first
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 on Server B
mysql> Change Master to master_host= ' 192.168.10.151 ', master_user= ' dbmysql ', maste
R_password= ' 123456 ', master_log_file= ' master-bin.000001 ', master_log_pos=107;

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_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 then view
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)

See the above error, the MySQL and the firewall from the server does not open 3306 ports, to server A and Server B to open 3306 ports, and then to view the state of MySQL from
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)

OK, you can see slave_io_running and slave_sql_running are yes, below also no error hint.

4. Verify
Create a database in the MySQL Lord
mysql> CREATE DATABASE Emlog;
Query OK, 1 row affected (0.01 sec)

And see if you have this database on MySQL.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Emlog |
| MySQL |
| Performance_schema |
| Test |
+--------------------+
5 rows in Set (0.02 sec)

You can see the replication has been synchronized, MySQL master and subordinate to build success.