系统
Centos6.3_32
查看系统版本
cat /proc/version
uname -a
cat /etc/issue
位数
getconf LONG_BIT
centos设置时间
date -s 06/22/96 将CentOS系统时间设定成下午1点52分0秒的命令 date -s13:52:00
看看是否安装mysql
rpm -qa|grep mysql
安装mysql
yum install mysql*
设置mysql root密码
mysql -u root –p
use mysql;
UPDATE user SET Password=PASSWORD('123456')where USER='root';
FLUSH PRIVILEGES;
quit;
service mysqld restart
mysql -u root –p123456
创建数据库
create database am;
创建用户
create user 'amuser'@'localhost' identifiedby 'amuser';
赋予远程访问
GRANT ALL ON am.* TO amuser IDENTIFIED BY"amuser";
查看用户及可以访问的主机（%表示所有主机都能访问到mysql）
select user,host from mysql.user;
+--------+-----------------------+
| user | host |
+--------+-----------------------+
| amuser | % |
| backup | 10.11.65.212 |
| root | 127.0.0.1 |
| | localhost |
| amuser | localhost |
| root | localhost |
| | localhost.localdomain |
| root | localhost.localdomain |
+--------+-----------------------+
8 rows in set (0.00 sec)
此时此用户可以本地登录，远程登录还需要设置iptables
vi /etc/sysconfig/iptables
添加一行
-I INPUT -p tcp --dport 3306 -j ACCEPT
或者-A RH-Firewall-1-INPUT -m state –state NEW -m tcp -p tcp –dport 3306-j ACCEPT
service iptables restart
即可用远程客户端工具登录
双击热备配置
在主服务器上，设置一个复制使用的账户，并授予REPLICATION SLAVE权限。这里创建一个复制用户backup，可以从IP为10.11.65.212的主机进行连接
GRANT REPLICATION SLAVE ON *.* TO 'backup'@'10.11.65.212'IDENTIFIED BY '123456';
在备服务器上，设置一个复制使用的账户，并授予REPLICATION SLAVE权限。这里创建一个复制用户backup，可以从IP为10.11.65.211的主机进行连接
GRANT REPLICATION SLAVE ON *.* TO 'backup'@'10.11.65.211'IDENTIFIED BY '123456';
主机（10.11.65.211）
vi /etc/my.cnf
[mysqld]
server-id=1
datadir=/var/lib/mysql
binlog-do-db=am
log-bin=mysql-bin.log
master-host=10.11.65.212
master-port=3306
master-user=backup
master-password=123456
replicate-do-db=am
master-connect-retry=60
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommendedto prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
备机（10.11.65.211）
vi /etc/my.cnf
server-id=2
datadir=/var/lib/mysql
binlog-do-db=am
log-bin=mysql-bin.log
master-host=10.11.65.211
master-port=3306
master-user=backup
master-password=123456
replicate-do-db=am
master-connect-retry=60
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommendedto prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
然后主备
service mysqld restart;
指定主备从哪一个日志开始同步
备机（10.11.65.212）
stop slave;
查看主机日志情况（10.11.65.211）
show master status\G;
*************************** 1. row***************************
File: mysql-bin.000004
Position: 364
Binlog_Do_DB: am
Binlog_Ignore_DB:
1 row in set (0.00 sec)
则备机指定同步所对应的日志（10.11.65.212）
CHANGE MASTER TO
MASTER_HOST='10.11.65.211',
MASTER_USER='backup',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=364;
主机做相应的操作（10.11.65.211）
stop slave;
查看主机日志情况（此时10.11.65.212为主）
show master status\G;
*************************** 1. row***************************
File: mysql-bin.000003
Position: 534
Binlog_Do_DB: am
Binlog_Ignore_DB:
1 row in set (0.00 sec)
ERROR:
No query specified
则备机指定同步所对应的日志（10.11.65.211）
CHANGE MASTER TO
MASTER_HOST='10.11.65.212',
MASTER_USER='backup',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=534;
然后主备show slave status\G;
主
*************************** 1. row***************************
Slave_IO_State: Waiting formaster to send event
Master_Host: 10.11.65.212
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 534
Relay_Log_File:mysqld-relay-bin.000002
Relay_Log_Pos: 679
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: am
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 534
Relay_Log_Space: 835
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
ERROR:
No query specified
备
*************************** 1. row***************************
Slave_IO_State: Waiting formaster to send event
Master_Host: 10.11.65.211
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 364
Relay_Log_File:mysqld-relay-bin.000003
Relay_Log_Pos: 466
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: am
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 364
Relay_Log_Space: 767
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
ERROR:
No query specified
如果主备的
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
则证明同步成功
如果报如下错误：
*************************** 1. row***************************
Slave_IO_State:
Master_Host: 10.11.65.212
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:mysql-bin.0000001
Read_Master_Log_Pos: 106
Relay_Log_File:mysqld-relay-bin.000002
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.0000001
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB: am
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
Relay_Log_Space: 106
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error1236 from master when reading data from binary log: 'Could not find first logfile name in binary log index file'
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
ERROR:
No query specified
则表示指定的日志文件不正确
解决方法：
备机
stop slave;
主机
Flush logs;
show master status;
备机
Change master to master_logfile=’ mysql-bin.000004’,master_log_pos=364;
start slave;
即可
扫一扫
509
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
