实战
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
[url]http://www.mysql.com[/url]
Support MySQL by buying support/licenses at [url]https://order.mysql.com[/url]
更改权限
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/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 51CTO博客，原文链接：http://blog.51cto.com/yahoon/37303，如需转载请自行联系原作者
