ITPub博客

首页 > Linux操作系统 > Linux操作系统 > mysql创建master/slave详细步骤

mysql创建master/slave详细步骤

原创 Linux操作系统 作者:myownstars 时间:2011-02-25 10:11:23 0 删除 编辑

尝试安装server
[justin@xen20-vm04 ~]$ rpm -ivh MySQL-server-5.1.51-1.glibc23.x86_64.rpm
error: Failed dependencies:
        MySQL conflicts with mysql-5.0.45-7.el5.x86_64
报错冲突
[justin@xen20-vm04 ~]$ rpm -qa | grep -i mysql
mysql-5.0.45-7.el5
卸载已有版本
[justin@xen19-vm04 ~]$ rpm -e mysql-5.0.45-7.el5
error: package mysql-5.0.45-7.el5 is not installed
进入root用户
[justin@xen19-vm04 ~]$ sudo su -
Password:
[root@xen20-vm04 justin]# rpm -e mysql-5.0.45-7.el5
[root@xen20-vm04 justin]#  rpm -qa | grep -i mysql

现在重新安装
[root@xen20-vm04 justin]# rpm -ivh MySQL-server-5.1.51-1.glibc23.x86_64.rpm
Preparing...                ########################################### [100%]
   1:MySQL-server           ########################################### [100%]

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h xen20-vm04 password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

Please report any problems with the /usr/bin/mysqlbug script!

Starting MySQL.[  OK  ]

安装完成后找不到/ect/my.cnf文件
Linux下用rpm包安装的MySQL是不会安装/etc/my.cnf文件的,
至于为什么没有这个文件而MySQL却也能正常启动和作用,在点有两个说法,
第一种说法,my.cnf只是MySQL启动时的一个参数文件,可以没有它,这时MySQL会用内置的默认参数启动,
第二种说法,MySQL在启动时自动使用/usr/share/mysql目录下的my-medium.cnf文件,这种说法仅限于rpm包安装的MySQL,

解决方法,只需要复制一个/usr/share/mysql目录下的.cnf文件到/etc目录,并改名为my.cnf即可

Master
my.cnf修改为以下内容 

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Default to using old password format for compatibility with mysql 3.x

# clients (those using the mysqlclient10 compatibility package).

old_passwords=1

server-id=1

log-bin

port=3306

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

slave
my.cnf修改以下内容 

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Default to using old password format for compatibility with mysql 3.x

# clients (those using the mysqlclient10 compatibility package).

old_passwords=1

innodb_file_per_table

server-id=2

#relay-log-purge=1

#report-host=hostname

read-only=1# 0 read/write 1 read only.

#log-bin=mysqld-bin.000001

log-slave-updates

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid


分别重启两个节点
[root@xen20-vm04 etc]# service mysql restart
Shutting down MySQL....[  OK  ]
Starting MySQL.[  OK  ]

此时输入mysql,报以下错误
[root@xen19-vm04 ~]# mysql
-bash: mysql: command not found
还需要安装客户端
[root@xen19-vm04 ~]# rpm -ivh MySQL-client-5.1.51-1.glibc23.x86_64.rpm
Preparing...                ########################################### [100%]
   1:MySQL-client           ########################################### [100%]
然后可以成功运行

首先在master节点运行
[root@xen19-vm04 justin]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.51-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant replication slave on *.* to 'rep'@'%' identified by 'rep';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      303 |           |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

在slave端进行如下操作
关闭服务
[root@xen20-vm04 mysql]# service mysql stop
Shutting down MySQL....[  OK  ]
进入my.cnf中datadir指向的目录
[root@xen20-vm04 mysql]# pwd
/var/lib/mysql
[root@xen20-vm04 mysql]# rm * -rf
[root@xen20-vm04 mysql]# ls
将master目录/var/lib/mysql下的文件全部copy过来

此时解除master节点的锁定

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

启动slave端的服务
[root@xen20-vm04 mysql]# service mysql start
Starting MySQL.[  OK  ]

[root@xen20-vm04 mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.51-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> change master to
    -> master_host='*.*.*.*',
    -> master_user='rep',
    -> master_password='rep',
    -> master_log_file='mysql-bin.000002',
    -> master_log_pos=303;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

转到master库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| #mysql50#.ssh      |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> use test;
Database changed
mysql> create table testrep(i int);
Ctrl-C -- sending "KILL QUERY 4" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
mysql> create table test(i int);
Ctrl-C -- sending "KILL QUERY 4" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
mysql> show tables;
Empty set (0.00 sec)

mysql> create database justin;

发现主库不能执行DDL

查看slave节点

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: *.*.*.*
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 303
               Relay_Log_File: xen20-vm04-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: No
            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: 303
              Relay_Log_Space: 106
              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: 1593
                Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

ERROR:

依据提示
分别查看master和slave节点的serverid配置
Master
[justin@xen19-vm04 etc]$ more my.cnf | grep server-id
server-id       = 1
#server-id       = 2
Slave
[justin@xen20-vm04 etc]$ more my.cnf | grep server-id
server-id=2
server-id       = 1
#server-id       = 2
果然配置很纠结
将slave的server-id设置为2,然后重启服务,再次查看
mysql> show slave status\G;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    3
Current database: *** NONE ***

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: *.*.*.*
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 393
               Relay_Log_File: xen20-vm04-relay-bin.000003
                Relay_Log_Pos: 341
        Relay_Master_Log_File: mysql-bin.000002
             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: 393
              Relay_Log_Space: 501
              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:
1 row in set (0.00 sec)
已经好了,至此master/slave已经搭建成功

测试一把
在master节点端
mysql> create table justin(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into justin values(1);
Query OK, 1 row affected (0.00 sec)
然后查看slave节点

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| justin         |
+----------------+
1 row in set (0.00 sec)

mysql> select * from justin;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> show create table justin;
+--------+--------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                               |
+--------+--------------------------------------------------------------------------------------------+
| justin | CREATE TABLE `justin` (
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+--------+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
搞定了

 

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-687909/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3113182