mysql主从 机器名_Mysql主从配置（一台机器）

(1)源码安装mysql主

cd /usr/local/

wget [http://mirrors.sohu.com/mysql/MySQL-5.1/mysql-5.1.73-linux-i686-glibc23.tar.gz](http://mirrors.sohu.com/mysql/MySQL-5.1/mysql-5.1.73-linux-i686-glibc23.tar.gz)

mkdir /usr/local/mysql

tar -zxvf [mysql-5.1.73-linux-i686-glibc23.tar.gz](http://mirrors.sohu.com/mysql/MySQL-5.1/mysql-5.1.73-linux-i686-glibc23.tar.gz)

mv /usr/local/src/mysql-5.1.73-linux-i686-glibc23/* /usr/local/mysql

useradd -s /sbin/nologin mysql

mkdir -p /data/mysql

chown mysql:mysql /data/mysql

cd /usr/local/mysql

./scripts/mysql_install_db --user=mysql --datadir=/data/mysql

cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

vim /etc/init.d/mysqld

找到basedir和datadir修改如下：

basedir=/usr/local/mysql/

datadir=/data/mysql/

vim /etc/profile

export PATH=$PATH:/usr/local/mysql/bin

source /etc/profile

(2)配置mysql主

vim /etc/my.cnf

server-id=1

log-bin= mysql-binlog

binlog-do-db=db1

mysql -uroot -S /tmp/mysql.sock

create database db1

use db1

create table lk

grant replication slave on *.* to 'repl'@'127.0.0.1' identified by '123456';

show master status;#记住file和position

flush tables with read lock;

mysqldump -uroot -S /tmp/mysql.sock db1 > db1.sql

然后在主mysql上解read only锁

mysql -uroot -S /tmp/mysql.sock -e "unlock tables"

(3)源码安装mysql从

cd /usr/local

cp -r mysql mysql2

cd mysql2

./scripts/mysql_install_db --user=mysql --datadir=/data/mysql2

cp /etc/my.cnf ./my.cnf

(4)配置mysql从

vim my.cnf

更改 port 为 3307 以及 socket 为/tmp/mysql2.sock

port = 3307

socket = /tmp/mysql2.sock

[mysqld]

port = 3307

socket = /tmp/mysql2.sock

server-id不能和主的重复

server-id = 2

relay-log= mysql-relay-bin

这一步非常重要，不然第二个mysql会不能启动

basedir=/usr/local/mysql2/

datadir=/data/mysql2/

导入数据库保证主从一致

mysql -uroot -S /tmp/mysql2.sock

create database db1;

mysql -uroot -S /tmp/mysql2.sock db1 < db1.sql

mysql -uroot -S /tmp/mysql2.sock

slave stop;

change master to master_host='127.0.0.1',master_port=3306,master_user='repl',master_password='123456', master_log_file='mysql-bin.000002', master_log_pos=282;

slave start;

show slave status\G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

(5)测试主从

mysql -uroot -S /tmp/mysql2.sock

use db1;

show tables;

mysql -uroot -S /tmp/mysql.sock

use db1

drop table lk

create table wr;

create database bbb;

mysql -uroot -S /tmp/mysql2.sock

use db1

show tables;

show databases;

可以看到已经同步了删除了表lk，创建了表wr，但是database bbb是没有同步的，因为我们只设置了要同步db1