mysql的增删改查 都会了,有个朋友向我请教数据库的主从备份上的问题,没有搭建过,今天正好有时间,就搭建了下,把其中搭建过程中遇到的问题,总结了下,和大家分享下: mysql数据库的安装 就不在这赘述了 ,首先要有两个mysql服务器: 系统和数据库版本是:centos5.5 mysql 5.0.45 masterIP 192.168.11.20 slaveIP 192.168.11.2 master主机上有以下操作: 修改/etc/my.cnf文件 # The MySQL server [mysqld] server-id=1 //主机标识,整数 log-bin=mysql-bin //确保此文件可写 binlog-do-db=beifen //要备份的数据,可以写多个 binlog-do-db=cong binlog-ignore-db=bubeifen //不需要备份的,也可以写多个 //创建同步用户 mysql> grant replication slave,reload,super on *.* to 'cong'@'%' identified by '123456'; Query OK, 0 rows affected (0.00 sec) [root@localhost ~]# mysql -u cong -p123456 -h 192.168.11.20 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.0.45-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select user(); +--------------------+ | user() | +--------------------+ | cong@192.168.11.20 | +--------------------+ 1 row in set (0.00 sec) //slave主机上的操作: # The MySQL server [mysqld] server-id=2 master-host=192.168.11.20 //master主机IP master-user=cong //同步用户 master-password=123456 //同步密码 然后重启master和slave上的mysql数据库 //在master上 mysql> show master status \G *************************** 1. row *************************** File: mysql-bin.000008 Position: 98 Binlog_Do_DB: beifen,cong Binlog_Ignore_DB: bubeifen 1 row in set (0.04 sec) //在slave上查看 mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.11.20 Master_User: cong Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 98 Relay_Log_File: localhost-relay-bin.000002 Relay_Log_Pos: 235 Relay_Master_Log_File: mysql-bin.000008 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: 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: 98 Relay_Log_Space: 235 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 1 row in set (0.00 sec) mysql> load data from master; Query OK, 0 rows affected, 2 warnings (10.10 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | cong | | beifen | | mysql | | test | +--------------------+ 8 rows in set (0.00 sec) 发现数据库cong,beifei这两个数据库 都同步过来了。 mysql> show processlist \G *************************** 1. row *************************** Id: 12 User: system user Host: db: NULL Command: Connect Time: 1742 State: Waiting for master to send event Info: NULL *************************** 2. row *************************** Id: 13 User: system user Host: db: NULL Command: Connect Time: 1729 State: Has read all relay log; waiting for the slave I/O thread to update it Info: NULL *************************** 3. row *************************** Id: 17 User: root Host: localhost db: NULL Command: Query Time: 0 State: NULL Info: show processlist 3 rows in set (0.00 sec) //OK了,mysql主从搭建成功了。 在搭建过程中,遇到以下问题: 别的都弄好了,但是slave无法同步上master上的数据库,我用cong这个用户登陆到master上,发现权限太小,就给cong这个用户赋予了些权限,这样就可以了。 mysql> GRANT SELECT,UPDATE,DROP,CREATE,INSERT,ALTER,INDEX ON *.* -> TO 'cong'@'%' IDENTIFIED BY '123456' -> WITH GRANT OPTION; Query OK, 0 rows affected (0.10 sec) (责任编辑:IT) |