CentOS6.4 配置mysql服务器启动多个端口，同步单表数据

============================================================

====多端口启动====

============================================================

创建数据目录

mkdir /usr/local/mysql/var1/

mkdir /usr/local/mysql/var2/

============================================================

赋权限

chown -R mysql:mysql /usr/local/mysql/var1/

chown -R mysql:mysql /usr/local/mysql/var2/

chown -R mysql:mysql /usr/local/mysql/var1/.

chown -R mysql:mysql /usr/local/mysql/var2/.

============================================================

复制和配置my.cnf

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

vi /etc/my3307.cnf

[client]

port = 3307

socket = /usr/local/mysql/mysql3307.sock

[mysqld]

port = 3307

socket = /usr/local/mysql/mysql3307.sock

----------

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

vi /etc/my3308.cnf

[client]

port = 3308

socket = /usr/local/mysql/mysql3308.sock

[mysqld]

port = 3308

socket = /usr/local/mysql/mysql3308.sock

============================================================

初始化数据库

cd /usr/local/mysql

scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/var1/

scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/var2/

============================================================

启动mysql要指定.cnf文件和目录启动

/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my3307.cnf --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/var1/ --user=mysql &

/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my3308.cnf --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/var2/ --user=mysql &

============================================================

设置root用户密码

mysqladmin -u root password '123456' -S /usr/local/mysql/mysql3307.sock -P 3307

登录

mysql -uroot -p123456 -S /usr/local/mysql/mysql3307.sock -P 3307

设置远程登录

GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "123456";

flush privileges;

----------

设置root用户密码

mysqladmin -u root password '123456' -S /usr/local/mysql/mysql3308.sock -P 3308

登录

mysql -uroot -p123456 -S /usr/local/mysql/mysql3308.sock -P 3308

设置远程登录

GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "123456";

flush privileges;

============================================================

停止服务

mysqladmin -h10.10.3.161 -uroot -p123456 -P3307 shutdown

============================================================

====同步单表====

============================================================

端口为3307的为主库，端口为3308的为从库

修改端口为3307的配置文件

vi /etc/my3307.cnf

server-id       = 1
log-bin=mysql-bin
replicate-do-db=ds
log-bin-index=mysql-bin.index
relay-log-index=mysql-relay-bin.index
relay-log=mysql-relay-bin

 修改端口为3308的配置文件

vi /etc/my3308.cnf

server-id       = 2
replicate-wild-do-table=ds.test
relay-log-index=dns-relay-bin.index
relay-log=dns-relay-bin

 

 重启服务

登录到端口为3307的主库上

mysql -uroot -p123456 -S /usr/local/mysql/mysql3307.sock -P 3307

查看主库的状态

show master status;

赋权限

GRANT REPLICATION SLAVE,FILE ON *.* TO 'root'@'10.10.3.161' IDENTIFIED BY '123456';

flush privileges;

登录到端口为3308的从库上

mysql -uroot -p123456 -S /usr/local/mysql/mysql3307.sock -P 3308

stop slave;

change master to master_host='10.10.3.161',master_user='root',master_password='123456',master_port=3307,master_log_file='mysql-bin.000001',master_log_pos=907;  (红字部份就是上面需要记住的两个值)

start slave

查看从库状态

show slave statusG;