mysql5.5 主从

yum -y install gcc gcc-c++ ncurses-devel

tar xvf cmake-2.8.4.gz 
cd make-2.8.8
./bootstrap 
gmake && gmake install
cd ..

tar zxvf mysql-5.5.25.tar.gz 
cd mysql-5.5.25
mkdir -p /usr/local/mysql/data
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS:STRING=utf8,gbk -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_DATADIR=/usr/local/mysql/data/
make && make install
chmod +w /usr/local/mysql/
chown -R mysql:mysql /usr/local/mysql/
ln -s /usr/local/mysql/lib/libmysqlclient.so.18 /usr/lib/libmysqlclient.so.18
cp support-files/my-large.cnf /usr/local/mysql/data/my.cnf
/usr/local/mysql/scripts/mysql_install_db --defaults-file=/usr/local/mysql/data/my.cnf --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/ --user=mysql
chmod +x /etc/init.d/mysqld 

vi /etc/init.d/mysqld 
	basedir=/usr/local/mysql
	datadir=/usr/local/mysql/data

chkconfig --add mysqld
chkconfig --level 35 mysqld on
/etc/init.d/mysqld start

cp /usr/local/mysql/bin/mysql /bin/mysql

mysql -uroot -p

为什么要是有 主从同步

在数据高可用的环境下单台数据库很难满足7*24的服务，需要有一台master主机作为唯一写入口和多台slave从机做查询

当一台数据库宕机的时候可以通过从服务器做恢复

运行原理

1.主库会将数据库的操作(DML)，对表结构的操作(DDL)对用户权限的操作(DCL)记录到日志中

2.slave I/O进程连接上master，请求获取从日志文件的指定位置的日志内容

3.master 接收到slave请求后，通过复制IO线程根据请求日志信息和位置返回给slave 的IO线程，slave IO线程接收到请求后，将日志写入到slave端的relay log末端，并将得到信息的概览写到master-info文件中，方便下次识别

4.slave 端检测到query log日志有更细后，使用slave sql进程把 relay log 中的日志在从服务器上冲洗执行一遍，使得数据和master保持一致

配置权限

create user 权限：用于创建删除用户

replication slave 权限：用于复制账号，且有 grant option

reload 权限 ： 执行flush logs的需求

super 或 replication client :执行show msater/slave status

grant replication slave,reload,create user,super on *.* to ‘replication‘@‘%‘ with grant option;

mater

vi /etc/my.cnf

[mysqld]
server-id       = 1
log-bin=mysql-bin
binlog-do-db=test
service mysqld restart

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl‘@‘%‘ IDENTIFIED BY ‘repl‘;
mysql> show master status\G
*************************** 1. row ***************************
            File: mysql-bin.000008
        Position: 192
    Binlog_Do_DB: test
Binlog_Ignore_DB:
1 row in set (0.00 sec)

slave

vi /etc/my.cnf

[mysqld]
server-id       = 2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
replicate-do-db=test
master-host=192.168.1.11
master-user=repl
master-password=rep1
master-port=3306

service mysqld restart

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.251
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 192
               Relay_Log_File: slave-relay-bin.000010
                Relay_Log_Pos: 337
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          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: 192
              Relay_Log_Space: 638
              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)

Master_Host: 192.168.0.251 指定主库的IP 地址

Master_User: rep1 指定从库用于复制主库上数据的用户

Master_Port: 3306 指定主库的端口

Slave_IO_Running: Yes 表示SLAVE IO 进程状态， YES 表示运行正常

Slave_SQL_Running: Yes 表是SLAVE SQL 进程状态，YES 表示运行正常

Seconds_Behind_Master: 0 表示从库落后主库多少秒，0 表是实时

Master_Log_File: mysql-bin.000009 IO 线程读取主库的日志文件名称

Read_Master_Log_Pos: 107 IO 线程读取主库的日志文件位置

Relay_Log_File: master-relay-bin.000021 : 表示从库应用relay log 日志名称

Relay_Log_Pos: 253 表示从库应用relay log 日志文件位置

**********************

test

master> use test;create table t(id int);

Query OK, 0 rows affected (0.09 sec)

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| t              |

+----------------+

1 row in set (0.00 sec)

slave>use test; show tables;

+----------------+

| Tables_in_test |

+----------------+

| t              |

+----------------+

1 row in set (0.00 sec)

**********************

错误

1.Error ‘The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement‘ on query. Default database: ‘test‘. Query: ‘grant select(TABLE_NAME,ENGINE) on test.t to ‘mogfee‘@‘localhost‘‘

vi /etc/my.cnf

skip-grant-tables

2.ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

flush privileges;

Got fatal error 1236 from master when reading data from binary log: ‘could not find next log; the first event ‘mysql-bin.000014‘ at 107, the last event read from ‘./mysql-bin.000014‘ at 630, the la

mysql5.5 主从,古老的榕树,5-wow.com