mysql5.1.73 主从复制的设置套路

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

[client]
#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-locking
key_buffer_size = 384M
max_allowed_packet = 128M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M

# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

server-id       = 1

[mysqldump]
quick
max_allowed_packet = 128M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout
 

   


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

[client]
#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-locking
key_buffer_size = 384M
max_allowed_packet = 128M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
 

# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

log-bin=mysql-bin

server-id       = 2

[mysqldump]
quick
max_allowed_packet = 128M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout
 

 

改完之后,两台鸡鸡都要重启一下mysql

service mysqld restart

登录一下主机的MYSQL

 mysql -u root -p

GRANT REPLICATION SLAVE ON *.* TO 'root' @'%' IDENTIFIED BY '密码';

SHOW MASTER STATUS;
查看一下position 的值

然后再另一个从服务器上配置

CHANGE MASTER TO MASTER_HOST='172.16.108.71',MASTER_USER='root',MASTER_PASSWORD='',MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=position的ID;

然后启动

START SLAVE;

查看状态,主要看看有没有什么错误

 SHOW DATABASES;

SHOW SLAVE STATUS;

 SHOW VARIABLES LIKE 'server_id'; --这个是检查ID是否重复
 

下面是测试工作:

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


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


  mysql> use t_test;
  Database changed


  mysql>create table t_test(id int(3),name varchar(20));
  Query OK, 0 rows affected (0.00 sec)
 
  mysql> insert into t_test values(23,'zhangs');
  Query OK, 1 row affected (0.00 sec)


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


在从服务器Mysql查询：


   mysql> show databases;


   +--------------------+
   | Database               |
   +--------------------+
   | information_schema |
   | t_test                 |      
   | mysql                 |
   | test          |
   +--------------------+
   4 rows in set (0.00 sec)


   mysql> use t_test
   Database changed
   mysql> select * from t_test;      //在从服务器上查看主服务器上新增的具体数据
   +------+-------+
   | id   | name  |
   +------+-------+
   |    23|zhangs|
   +------+------+
   1 row in set (0.00 sec)
 
关闭slave服务器： stop slave/start slave 
    再停止mysql 服务，service mysqld stop


    编写一shell脚本，用nagios监控slave的两个yes（Slave_IO及Slave_SQL进程），如发现只有一个或零个yes，就表明主从有问题了，发短信警报