首页 > 代码库 > CentOS6.4 配置mysql服务器启动多个端口,同步单表数据
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 status\G;
http://www.cnblogs.com/kgdxpr/p/3347562.html