mysql主从同步

• 环境

192.168.2.118 mysql主 CentOS release 6.3 (Final)
192.168.2.119 mysql从 CentOS release 6.3 (Final)

• 118服主mysql配置

在/etc/my.conf下添加

log-bin=mysql-bin
server-id = 1

进入mysql授权账号tongbu

grant replication slave, super,reload  on *.* to 'tongbu'@'192.168.2.119' identified by'123456';
flush privileges;

查看master当前状态

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      682 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.25 sec)

mysql>

• 119服从mysql配置

在/etc/my.conf下添加，值不能跟master上的一样

server-id = 2

指定118主mysql上的ip/用户名/密码/bin-log名/position位置点

change master to master_host='192.168.2.118', master_user='tongbu', master_password='123456', master_log_file='mysql-bin.000003', master_log_pos=682;

在slave进入mysql后，启动slave

slave start;
show slave status\G

如果io线程和sql线程均为显示yes，则表示slave和master成功连接并完成同步

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.2.118
                  Master_User: tongbu
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 682
               Relay_Log_File: leeclient-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              ……………

• 测试主从同步

在118主mysql上创建库和表并添加数据

create database tangchao;
use tangchao;
create table lingyange (id int,name char(20));
select * from lingyange;

然后在119上查看

select * from tangchao.lingyange;

即可看到数据有同步过来，至此主从同步配置完成。

---

备注：
1）mysql> use mysql;
ERROR 1044 (42000): Access denied for user ”@’localhost’ to database ‘mysql’
而查询数据库只显示两个

解决方法：mysql数据库的user表里，存在用户名为空的账户即匿名账户，导致登录的时候是虽然用的是root，但实际是匿名登录的
在/etc/my.conf下添加参数skip-grant-tables，即登陆mysql可以跳过密码
然后重启数据库
service mysqld restart
然后直接mysql进入后，修改root的密码
update mysql.user set password=password(‘123456’) WHERE User=’root’;
flush privileges;
此时就可以查看到mysql数据库和performance_schema数据库；

2）cmake安装时候提示
– MySQL 5.5.20
– Could NOT find Threads (missing: Threads_FOUND)
CMake Error at configure.cmake:152 (LIST):
list sub-command REMOVE_DUPLICATES requires list to be present.
Call Stack (most recent call first):
CMakeLists.txt:246 (INCLUDE)
解决方法：删除掉CMakeCache.txt文件，或者是yum install ncurses ncurses-devel -y
[root@localhost mysql-5.5.20]# rm CMakeCache.txt

3）启动mysql服务的时候提示
[root@localhost local]# service mysqld restart
env: /etc/init.d/mysqld: 权限不够
解决方法：chmod u+x /etc/init.d/mysqld