本例配置说明:
已安装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