实战mysql的安装与复制-sdccf-ChinaUnix博客

实战mysql的安装与复制

此文是对前文的一次扩展和补充.增加了很多内容以及过程中遇到问题的解决办法.

当前已经有一台linux(均为redhat)服务器,ip10.1.3.106运行mysql4.1.9,现在需要构建一个mysql的复制系统,当前的106作为主服务器,另外需要搭建一台复制服务器做从服务器.新服务器的ip为10.1.3.107

1.安装mysql

安装包为mysql-4.1.9.tar.gz(为了保证兼容性,采用的是与106相同的安装包)

按照安装文档的方法,按照如下顺序进行安装

The basic commands you must execute to install a MySQL source

distribution are:

增加用户

shell> groupadd mysql

shell> useradd -g mysql mysql

shell> tar –zxvf mysql-4.1.9.tar.gz

shell> cd mysql-4.1.9

编译安装,安装路径为/usr/local/mysql

shell> ./configure --prefix=/usr/local/mysql

shell> make

shell> make install

shell> cp support-files/my-medium.cnf /etc/my.cnf

shell> cd /usr/local/mysql

安装初始的表

shell> bin/mysql_install_db --user=mysql

信息如下

[root@localhost mysql]# bin/mysql_install_db --user=mysql

Installing all prepared tables

Fill help tables

To start mysqld at boot time you have to copy support-files/mysql.server

to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

To do so, start the server, then issue the following commands:

/usr/local/mysql/bin/mysqladmin -u root password 'new-password'

/usr/local/mysql/bin/mysqladmin -u root -h 机器名 password 'new-password'

See the manual for more instructions.

You can start the MySQL daemon with:

cd /usr/local/mysql ; /usr/local/mysql/bin/mysqld_safe &

You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory:

cd sql-bench ; perl run-all-tests

Please report any problems with the /usr/local/mysql/bin/mysqlbug script!

The latest information about MySQL is available on the web at

Support MySQL by buying support/licenses at

更改权限

shell> chown -R root .

shell> chown -R mysql var

shell> chgrp -R mysql .

启动服务

shell> bin/mysqld_safe --user=mysql &

信息如下

[root@localhost mysql]# bin/mysqld_safe --user=mysql &

[1] 25772

[root@localhost mysql]# Starting mysqld daemon with databases from /usr/local/mysql/var

修改MYSQL密码，默认安装密码为空，为了安全你必须马上为root设置密码例如设为abc
/usr/local/mysql/bin/mysqladmin -u root password abc

通过命令行登陆

/usr/local/mysql/bin/mysql –uroot –p

按提示输入密码即可

让它随系统启动时自动启动,在/etc/rc.d/rc.local中加入以下一行

/usr/local/mysql/bin/mysqld_safe --user=mysql &

停止mysql

/usr/local/mysql/bin/mysqladmin -uroot -p shutdown

按提示输入密码即可

这样mysql的安装就完成了.

2.配置复制

我按照我之前那篇文章配置好了之后,在从上面查看mysql> show slave status\G发现Slave_IO_State:这一项的内容一直都是connecting.在主上面查看root的权限,发现只能从本机登陆.为了安全起见,在主上面建了一个专门用于复制的账户允许它远程登陆

mysql > GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'slavepass';

可是问题变得更加严重,在从上面查看复制状态

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State:

Master_Host: 10.1.3.106

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: ifi-db01-bin.000019

Read_Master_Log_Pos: 4653237

Relay_Log_File: ifi-db02-relay-bin.000006

Relay_Log_Pos: 10072

Relay_Master_Log_File: ifi-db01-bin.000019

Slave_IO_Running: No

Slave_SQL_Running: Yes

Replicate_Do_DB:

…..

可以看到IO进程并没有运行,查看进程列表.(准确的说应该是线程,此处为了方便理解写为进程)

mysql> show processlist\G

*************************** 1. row ***************************

Id: 2

User: system user

Host:

db: NULL

Command: Connect

Time: 14

State: Has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

*************************** 2. row ***************************

Id: 8

User: root

Host: localhost

db: NULL

Command: Query

Time: 0

State: NULL

Info: show processlist

3 rows in set (0.00 sec)

也并没有I/0进程.

通过查看文档的FAQ,上面说要看MYSQL的错误日志.即数据目录下的"机器名.err"文件

[root@localhost var]# less xxx.err

070806 2:19:12 [Note] Slave I/O thread: connected to master 'repl@10.1.3.106:3306', replication started in log 'ifi-db01-bi

n.000019' at position 4540467

070806 2:19:12 [ERROR] While trying to obtain the list of slaves from the master '10.1.3.106:3306', user 'repl' got the foll

owing error: 'Access denied; you need the REPLICATION SLAVE privilege for this operation'

070806 2:19:12 [ERROR] Slave I/O thread exiting, read up to log 'ifi-db01-bin.000019', position 4540467

可见是repl用户的权限不足.明明增加了权限为什么还报这样的错误??刷新权限!!

在主上面执行

mysql > FLUSH PRIVILEGES;

然后在从上面执行

mysql > STOP SLAVE;

等一会让它完全停止

mysql > CHANGE MASTER TO

->MASTER_HOST='10.1.3.106',

->MASTER_USER='repl',

->MASTER_PASSWORD='slavepass',

->MASTER_LOG_FILE='ifi-db01-bin.000019',

->MASTER_LOG_POS=4540467;

启动复制

mysql > START SLAVE

这样就正常了,查看它们的信息如下

主上面的

mysql > SHOW PROCESSLIST\G

*************************** 22. row ***************************

Id: 4274

User: repl

Host: 10.1.3.107:32770

db: NULL

Command: Binlog Dump

Time: 1629

State: Has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

22 rows in set (0.00 sec)

从上面的

[root@ifi-db02 var]# less xxx.err

070806 3:28:51 [Note] Slave SQL thread initialized, starting replication in log 'ifi-db01-bin.000019' at position 4677741, r

elay log './ifi-db02-relay-bin.000006' position: 34576

070806 3:28:56 [Note] Slave I/O thread: connected to master 'repl@10.1.3.106:3306', replication started in log 'ifi-db01-bi

n.000019' at position 4677741

没有报错误了

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.1.3.106

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: ifi-db01-bin.000019

Read_Master_Log_Pos: 4677185

Relay_Log_File: ifi-db02-relay-bin.000006

Relay_Log_Pos: 34020

Relay_Master_Log_File: ifi-db01-bin.000019

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

…..

mysql> show processlist\G

*************************** 1. row ***************************

Id: 1

User: system user

Host:

db: NULL

Command: Connect

Time: 1709

State: Waiting for master to send event

Info: NULL

*************************** 2. row ***************************

Id: 2

User: system user

Host:

db: NULL

Command: Connect

Time: 24

State: Has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

*************************** 3. row ***************************

Id: 33

User: root

Host: localhost

db: NULL

Command: Query

Time: 0

State: NULL

Info: show processlist

3 rows in set (0.00 sec)

可见所有进程都已经正常启动了.

附件是更新了的<服务器复制配置>>一文

本文出自 “yahoon的小屋” 博客，请务必保留此出处http://yahoon.blog.51cto.com/13184/37303