嘗試安裝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 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)

在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> 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)
搞定了

mysql建立master/slave詳細步驟

相關文章