mysql配置主从

本例配置说明：

    已安装mysql，
   本例中mysql的数据存储目录为：/opt/data/mysql，
    日志目录为：/opt/log/mysql

一 创建相应的数据日志存储目录

service mysqld stop  #先停止mysql服务
rm -rf /opt/data/mysql #删除原有的mysql目录
mkdir  -p  /opt/data/mysql1 #创建主库数据存储目录
mkdir  -p  /opt/data/mysql2 #创建从裤数据存储目录
​
chown -R mysql.mysql /opt/data/mysql1  #修改用户属性为mysql
chown -R mysql.mysql /opt/data/mysql2
​
rm -rf /opt/log/mysql #删除原有的mysql日志目录
mkdir -p /opt/log/mysql1 #创建主库日志目录
mkdir -p /opt/log/mysql2 #创建从裤日志目录
​
chown -R mysql.mysql /opt/log/mysql1   #修改用户属性为mysql
chown -R mysql.mysql /opt/log/mysql2
​
mkdir -p /var/run/mysql1    #创建目录，并修改属性
chown -R mysql.mysql /var/run/mysql1
mkdir -p /var/run/mysql2
chown -R mysql.mysql /var/run/mysql2
mkdir -p /dev/shm/mysql1
mkdir -p /dev/shm/mysql2
chown -R mysql.mysql /dev/shm/mysql1
chown -R mysql.mysql /dev/shm/mysql2
​
\cp -r my.cnf /etc/mysql/my.cnf  #替换原有的my.cnf配置文件
​
\cp -r mysqld_multi /dom/mysql/bin/mysqld_multi  #替换原有的mysqld_multi文件
ln -sf /dom/mysql/bin/mysqld_multi /etc/init.d/mysqld #设置软连接
​
/dom/mysql/bin/mysql_install_db --basedir=/dom/mysql/ --datadir=/dom/datalink/mysql1/  #初始化数据库
/dom/mysql/bin/mysql_install_db --basedir=/dom/mysql/ --datadir=/dom/datalink/mysql2/
         
chown -R mysql.mysql /opt/data/mysql1  #依次修改用户属性
chown -R mysql.mysql /opt/data/mysql2 
chown -R mysql.mysql /opt/log/mysql1
chown -R mysql.mysql /opt/log/mysql2
chown -R mysql.mysql /var/run/mysql1
chown -R mysql.mysql /var/run/mysql2
chown -R mysql.mysql /dev/shm/mysql1
chown -R mysql.mysql /dev/shm/mysql2

二 启动数据库

service mysqld start 1  #启动主库
service mysqld start 2  #启动从库

三 配置完成后修改新的数据库密码

mysqladmin -uroot password '******' -P13507 -S /dev/shm/mysql1/mysql.sock 
mysqladmin -uroot password '******' -P13508 -S /dev/shm/mysql2/mysql.sock  

四 配置同步账户和权限

1 登陆端口为13507的主库，执行：
    mysql -uroot -p'impadmin' -P13507 -S /dev/shm/mysql1/mysql.sock
2 建立一个同步用户，执行2条命令：
    grant replication slave, replication client on *.* to repl@'localhost' identified by 'repl';
    quit;
​
3 登陆端口为13508的从库，
    mysql -uroot -p'impadmin' -P13508 -S /dev/shm/mysql2/mysql.sock
-- 设置主库同步信息，执行2条命令：
    change master to master_host='localhost', MASTER_PORT=13507, master_user='repl', master_password='repl', master_log_file='mysql-bin.000001', master_log_pos=0;
    start slave;
    quit;
其中MASTER_PORT为主库的端口，master_user主库同步用户名，master_password主库同步密码。
重启数据库服务后。
​
4 登陆主库， 执行：
    show master status \G
-- 执行：
    show processlist;
如果看到 repl 用户的State状态为：Master has sent all binlog to slave; watting...  说明主库配置成功（主从是根据bin-log进程同步的）。
​
5 登录从库，执行：
    show slave status \G
可以看到Slave_IO_State:watting for master to send event ；
-- 执行：
    show processlist;
如果看到
    system user        Waitting for master to send event 
    system user        Slave has read all .........
则说明从库配置成功

五 对数据库进行配置

1.  登录13507数据库，执行
    mysql -uroot -p'impadmin' -P13507 -S /dev/shm/mysql1/mysql.sock
2.  创建数据库用户，执行
    grant all privileges on *.* to imp_admin@"%" identified by 'paswrd';
    grant all privileges on *.* to imp_admin@"localhost" identified by 'paswrd';
​
    flush privileges;
    create database imp_commondb;
    create database imp_monitordb;
    create database imp_scheduledb;
    quit;
3.  登录13508数据库，执行
    mysql -uroot -p'impadmin' -P13508 -S /dev/shm/mysql2/mysql.sock
4.  创建数据库用户，执行
    grant all privileges on *.* to imp_admin@"%" identified by 'caimP@*&56';
    grant all privileges on *.* to imp_admin@"localhost" identified by 'caimP@*&56';
    flush privileges;
    quit;
5.  配置完成。
6.  替换etc/mysql目录下的my.cnf文件
    grant all privileges on *.* to imp_admin@'%' identified by 'caimP@*&56';
    flush privileges;
​

六 my.cnf配置文件

[mysqld_multi]
mysqld = /dom/mysql/bin/mysqld_safe
mysqladmin = /dom/mysql/bin/mysqladmin
​
#主库配置 
[mysqld1]
basedir = /dom/mysql
socket = /dev/shm/mysql1/mysql.sock
port = 13507  
pid-file = /var/run/mysql1/mysql.pid
datadir = /dom/datalink/mysql1
log-bin = /dom/loglink/mysql1/mysql-bin
relay-log = /dom/loglink/mysql1/relay-log
relay-log-index = /dom/loglink/mysql1/relay
server-id = 1
default-storage-engine=innodb
lower_case_table_names=1
​
binlog-ignore-db = mysql  #标记不做主从的数据库
binlog-ignore-db = information_schema
binlog-ignore-db = cdp_dbmodel
binlog-ignore-db = dba_dbmodel
binlog-ignore-db = emm_dbmodel
binlog-ignore-db = emm_imserver
binlog-ignore-db = ets_dbmodel
binlog-ignore-db = impdb
binlog-ignore-db = oma_dbmodel
binlog-ignore-db = performance_schema
binlog-ignore-db = tmp_dba_dest
binlog-ignore-db = tmp_dba_src
binlog-ignore-db = test
​
#从库配置
[mysqld2]
basedir = /dom/mysql
socket = /dev/shm/mysql2/mysql.sock
port = 13508
pid-file = /var/run/mysql2/mysql.pid
datadir = /dom/datalink/mysql2
log-bin = /dom/loglink/mysql2/mysql-bin
relay-log = /dom/loglink/mysql2/relay-log
relay-log-index = /dom/loglink/mysql2/relay
server-id = 2
slave-skip-error=all
default-storage-engine=innodb
lower_case_table_names=1

附件：mysqld_multi下载