- 论坛徽章:
- 0
|
mysql的安装
准备：
根据甲方提供的my.cnf修订其数据库所在目录(其他不变)后放置于缺省位置:/etc/my.cnf
建立业务所需目录：mkdir -p /data/mysql ;chown -R mysql:mysql /data
修订权限chmod 700 /data/mysql
A 安装
master是45 backup是40
[root@r510 mysql]# rpm -ivh MySQL-*.rpm
Preparing...
/etc/init.d/mysql start
/usr/bin/mysql_secure_installation设置root密码和移除匿名用户，test库
B 远程登录授权
use mysql;select host,user from user ;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '2011mlnsh' WITH GRANT OPTION ;
FLUSH PRIVILEGES;
select host,user,password from user;
mysql> show databases; show engines; show matser status; show status\G; show processlist\G;
--------------------
| Database |
--------------------
| information_schema |
| milanoo |
| myphpbb |
| mysql |
| performance_schema |
--------------------
5 rows in set (0.01 sec)
测试登录：mysql -h x.x.x.x -u root -p
C 配置同步
c1 在master上：
cat /etc/my.cnf 确保有以下的行存在，也是要唯一。
server-id=1
log-bin=mysql-bin
mysql>use mysql;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY '2011mlnshrep';
select host,user from user ;
mysql> show master status; #查看主节点的状态 记录log（file）和Position
mysql> show master status;
--------------- ---------- -------------- ------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
--------------- ---------- -------------- ------------------
| binlog.000005 | 107 | | |
--------------- ---------- -------------- ------------------
1 row in set (0.00 sec)
mysql> show processlist;
---- ------ --------------------- ------ ------------- --------- ----------------------------------------------------------------------- ------------------
| Id | User | Host | db | Command | Time | State | Info |
---- ------ --------------------- ------ ------------- --------- ----------------------------------------------------------------------- ------------------
| 4 | rep1 | 10.10.10.238:35021 | NULL | Binlog Dump | 1121604 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 36 | root | localhost | NULL | Query | 0 | NULL | show processlist |
---- ------ --------------------- ------ ------------- --------- ----------------------------------------------------------------------- ------------------
2 rows in set (0.00 sec)
c2 在Slave服务器上：
cat /etc/my.cnf,确保有以下的行存在,也是要唯一。 不过据网上消息介绍，Mysql版本从5.1.7以后开始就不支持“master-host”类似的参数;
[mysqld]
server-id=2
report-host=10.10.10.238
replicate-ignore-db=mysql
skip-slave-start
mysql>change master to master_host='10.10.10.237',master_user='rep1',master_password='2011mlnsh',master_log_file='',master_log_pos=107;
Query OK, 0 rows affected (0.02 sec)
mysql> Slave start;
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果出现错误：
Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
用以下步骤解决：
slave stop;
reset slave;
slave start;
如果出现错误：
Slave_IO_Running: Yes
Slave_SQL_Running: No
用以下步骤解决：
解决办法一、
Slave_SQL_Running: No
1.程序可能在slave上进行了写操作
2.也可能是slave机器重起后，事务回滚造成的.
一般是事务回滚造成的：
解决办法：
mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> slave start;
解决办法二、
首先停掉Slave服务：slave stop
到主服务器上查看主机状态：
记录File和Position对应的值
进入master
mysql> show master status;
---------------------- ---------- -------------- ------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
---------------------- ---------- -------------- ------------------
| localhost-bin.000094 | 33622483 | | |
---------------------- ---------- -------------- ------------------
1 row in set (0.00 sec)
然后到slave服务器上执行手动同步：
mysql> change master to
> master_host='master_ip',
> master_user='user',
> master_password='pwd',
> master_port=3306,
> master_log_file=localhost-bin.000094',
> master_log_pos=33622483 ;
1 row in set (0.00 sec)
mysql> slave start;
1 row in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
........
Master_Log_File: localhost-bin.000094
Read_Master_Log_Pos: 33768775
Relay_Log_File: localhost-relay-bin.000537
Relay_Log_Pos: 1094034
Relay_Master_Log_File: localhost-bin.000094
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
手动同步需要停止master的写操作！
c3 这时在主服务器上有binlog dump的进程：
mysql> show processlist;
---- ------ --------------------- ------ ------------- --------- ----------------------------------------------------------------------- ------------------
| Id | User | Host | db | Command | Time | State | Info |
---- ------ --------------------- ------ ------------- --------- ----------------------------------------------------------------------- ------------------
| 4 | rep1 | 10.10.10.238:35021 | NULL | Binlog Dump | 1121604 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 36 | root | localhost | NULL | Query | 0 | NULL | show processlist |
---- ------ --------------------- ------ ------------- --------- ----------------------------------------------------------------------- ------------------
2 rows in set (0.00 sec)
c4 测试
在master上：
#测试库1
CREATE DATABASE `testdb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
use `testdb`;
CREATE TABLE `testtab` (
`name` char(64) NOT NULL DEFAULT '',
`phone` char(64) NOT NULL DEFAULT '',
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='test tables';
insert into `testtab`(`name`,`phone`) VALUES('suanXing','15608188873');
在master和slave上查询应该是以下相同结果：
use testdb; select * from testtab;
mysql> select * from testtab;
---------- -------------
| name | phone |
---------- -------------
| YuanXing | 15608188773 |
---------- -------------
1 row in set (0.00 sec)
c4 导入milanoo库:由甲方操作我们配合
在master上：
建库：
mysql> use mysql;
mysql>CREATE DATABASE `milanoo` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
授权：
mysql>GRANT ALL ON `milanoo`.* TO 'milanoo'@'%' IDENTIFIED BY 'milanootest';
确认：mysql> use mysql;select host,user,password from user ;show databases;
c5 读写分离：
在从库上建立设只读用户以及授权：由甲方操作我们配合
---------------------------------------------------------------------------------------------- |
|
免费注册
发表于 2011-12-21 08:42