centOS6.5环境下mysql主从配置步骤

1、主机配置

1.1、mysql版本一致

2、修改主服务器master:
   #vi /etc/my.cnf
[client]

password= 123456

port= 3306

default-character-set=utf8

[mysqld]

port= 3306

character_set_server=utf8

character_set_client=utf8

collation-server=utf8_general_ci

lower_case_table_names=1

log-bin=mysql-bin

binlog_format=mixed

server-id= 222   //[必须]服务器唯一ID，默认是1，一般取IP最后一段

max_connections= 6000

join_buffer_size= 128M

sort_buffer_size= 2M

read_rnd_buffer_size= 2M

innodb_buffer_pool_size= 4G

sql_mode=NO_ENGINE_SUBSTITUTION,ANSI

[mysql]

default-character-set= utf8 

3、修改从服务器slave:
   #vi /etc/my.cnf
      [client]

password= 123456

port= 3306

default-character-set=utf8

[mysqld]

port= 3306

character_set_server=utf8

character_set_client=utf8

collation-server=utf8_general_ci

lower_case_table_names=1

max_connections= 6000

join_buffer_size= 128M

sort_buffer_size= 2M

read_rnd_buffer_size= 2M

innodb_buffer_pool_size= 4G

log-bin=mysql-bin

binlog_format=mixed

server-id= 223        //[必须]服务器唯一ID，默认是1，一般取IP最后一段

relay_log= /var/lib/mysql/mysql-relay-bin

log_slave_updates= 1

read_only= 1

 

sql_mode=NO_ENGINE_SUBSTITUTION,ANSI

[mysql]

default-character-set= utf8 

4、重启两台服务器的mysql
   /etc/init.d/mysql restart

5、在主服务器上建立帐户并授权slave:

  #/usr/local/mysql/bin/mysql –u oot -p  

   mysql>GRANT REPLICATION SLAVEON *.* to 'mysync'@'%' identified by 'q123456'; //一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如172.16.5.223，加强安全。

6、登录主服务器的mysql，查询master的状态
   mysql>show master status;
   +------------------+----------+--------------+------------------+
   |File             |Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000004 |      308|             |                 |
   +------------------+----------+--------------+------------------+
   1 row in set (0.00 sec)
   注：执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

7、配置从服务器Slave：
   mysql>change master to mster_host='172.16.5.222',master_user='mysync',master_password='q123456',

        master_log_file='mysql-bin.000004',master_log_pos=308;   //注意不要断开，“308”无单引号。

   Mysql>start slave;    //启动从服务器复制功能

8、检查从服务器复制功能状态：

   mysql> show slave status \G

   *************************** 1. row***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.2.222  // 主服务器地址

                  Master_User: myrync         //授权帐户名，尽量避免使用root

                  Master_Port: 3306          //数据库端口，部分版本没有此行

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000004

          Read_Master_Log_Pos: 600        //#同步读取二进制日志的位置，大于等于>=Exec_Master_Log_Pos

               Relay_Log_File: ddte-relay-bin.000003

                Relay_Log_Pos: 251

         Relay_Master_Log_File:mysql-bin.000004

             Slave_IO_Running: Yes       //此状态必须YES

            Slave_SQL_Running: Yes       //此状态必须YES
                   ......

注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。

以上操作过程，主从服务器配置完成。
  
9、主从服务器测试：

主服务器Mysql，建立数据库，并在这个库中建表插入一条数据：

  mysql> create database hi_db;
  Query OK, 1 row affected (0.00 sec)

  mysql> use hi_db;
  Database changed

  mysql>  create table hi_tb(id int(3),name char(10));
  Query OK, 0 rows affected (0.00 sec)
 
  mysql> insert into hi_tb values(001,'bobu');
  Query OK, 1 row affected (0.00 sec)

  mysql> show databases;
   +--------------------+
   |Database           |
   +--------------------+
   | information_schema |
   |hi_db             |
   |mysql             |
   |test              |
   +--------------------+
   4 rows in set (0.00 sec)

从服务器Mysql查询：

   mysql> show databases;

   +--------------------+
   |Database           |
   +--------------------+
   | information_schema |
   |hi_db             |          //I'M here，大家看到了吧
   |mysql             |
   |test              |
   +--------------------+
   4 rows in set (0.00 sec)

   mysql> use hi_db
   Database changed
   mysql> select * from hi_tb;    //可以看到在主服务器上新增的具体数据
   +------+------+
   | id   | name |
   +------+------+
   |    1 | bobu |
   +------+------+

   1 row in set (0.00 sec)

j.可能遇到的错误（一）

2014-01-21 06:03:29 14964 [ERROR] InnoDB: auto-extendingdata file ./ibdata1 is of a different size 640 pages (rounded down to MB) thanspecified in the .cnf file: initial 768 pages, max 0 (relevant if      non-zero) pages!

2014-01-21 06:03:29 14964 [ERROR] InnoDB: Could not openor create the system tablespace. If you tried to add new data files to thesystem tablespace, and it failed here, you  should now edit innodb_data_file_path in my.cnf back to what it was, andremove the new ibdata files InnoDB created in this failed attempt. InnoDB onlywrote those files full of  zeros, but didnot yet use them in any way. But be careful: do not remove old data files whichcontain your precious data!

2014-01-21 06:03:29 14964 [ERROR] Plugin 'InnoDB' initfunction returned error.

2014-01-21 06:03:29 14964 [ERROR] Plugin 'InnoDB'registration as a STORAGE ENGINE failed.

2014-01-21 06:03:29 14964 [ERROR] Unknown/unsupportedstorage engine: InnoDB

2014-01-21 06:03:29 14964 [ERROR] Aborting

在/var/lib/mysql/目录下删掉这三个文件：ibdata1 ib_logfile0 ib_logfile1 然后重启mysql

cd /var/lib/mysql

rm ibdata1 ib_logfile0 ib_logfile1

service mysql start

k.可能遇到的错误（二）

[root@localhost local]# mysql -uroot -p

Enter password: 

ERROR 1045 (28000): Access denied for user'root'@'localhost' (using password: YES)

方法操作很简单，如下：

# /etc/init.d/mysql stop

# mysqld_safe --user=mysql --skip-grant-tables--skip-networking &

# mysql -u root mysql

//把空的用户密码都修改成非空的密码。

mysql> UPDATE user SETPassword=PASSWORD('newpassword') where USER='root' and host='root' orhost='localhost';

mysql> FLUSH PRIVILEGES;

mysql> quit # /etc/init.d/mysqld restart

# mysql -uroot -p

Enter password: <输入新设的密码newpassword>

l.可能遇到的错误（三）

mysql> show databases;

ERROR 1820 (HY000): You must SET PASSWORD beforeexecuting this statement

这句话要求你重新设置一次密码！

mysql>  SET PASSWORD = PASSWORD('123456');

Query OK, 0 rows affected (0.03 sec)

mysql> create database roger;

Query OK, 1 row affected (0.00 sec)