mysql注册slave_mysql创建master/slave详细步骤

尝试安装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)

搞定了