root 1369 0.2 0.0 106060 1484 pts/0 S 01:00 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/master.pid
mysql 1481 1.5 0.5 265280 21612 pts/0 Sl 01:00 0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --user=mysql --log-error=/data/mysql/master.err --pid-file=/data/mysql/master.pid --socket=/tmp/mysql.sock --port=3306
root 1494 0.0 0.0 103248 872 pts/0 S+ 01:00 0:00 grep mysql
查看端口：
[root@master mysql]# netstat -lnp | grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1481/mysqld
unix 2 [ ACC ] STREAM LISTENING 18672 1481/mysqld /tmp/mysql.sock
从：192.168.134.129（主从MySQL的安装配置过程一模一样，这里不再赘述）
登录MySQL有三种方式：
1.使用绝对路径登录：
/usr/local/mysql/bin/mysql
2.使用socket登录：
mysql -S /tmp/mysql.sock
3.使用host+port登录：
mysql -h127.0.0.1 -P3306
默认都是没有密码的，可以使用mysqladmin设置密码。
3.开始搭建主从MySQL：
主：192.168.134.128
将MySQL加入到环境变量中：
[root@master mysql]# vim /etc/profile.d/mypath.sh
export PATH=$PATH:/usr/local/mysql/bin/
[root@master mysql]# source /etc/profile.d/mypath.sh
登录MySQL创建数据库db1：
[root@master mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.73 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
先退出mysql：
mysql> quit
拷贝mysql库到db1库：
备份到123.sql：
[root@master mysql]# mysqldump -S /tmp/mysql.sock mysql > 123.sql
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
恢复到db1：
[root@master mysql]# mysql -S /tmp/mysql.sock db1 < 123.sql
再次登录MySQL，查看db1中的内容：
[root@master mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.73 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use db1;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_db1 |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
23 rows in set (0.00 sec)
mysql> quit
Bye
说明db1数据库创建成功。
编译配置文件：
[root@master mysql]# vim /etc/my.cnf
打开log-bin前面的注释：
log-bin=mysql-bin
并在其下面添加一行（表示只对db1做主从）：
binlog-do-db=db1
（多个数据可以用逗号分隔：binlog-do-db=db1,db2,db3，或者使用黑名单形式：binlog-ignore-db=db1）
重启MySQL：
[root@master mysql]# /etc/init.d/mysqld restart
Shutting down MySQL... SUCCESS!
Starting MySQL. SUCCESS!
查看/data/mysql/下的内容：
[root@master mysql]# ls /data/mysql
db1 ibdata1 ib_logfile0 ib_logfile1 master.err master.pid mysql mysql-bin.000001 mysql-bin.index test
发现二进制日志文件mysql-bin.000001已经生成。
登录mysql：
[root@master mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.73-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
以replication权限授权给从MySQL上一个用户slave密码123abc：
mysql> grant replication slave on *.* to 'slave'@'192.168.134.129' identified by '123abc';
Query OK, 0 rows affected (0.00 sec)
刷新权限：
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
先把表锁起来：
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
显示主MySQL的状态：
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 338 | db1 | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
从：192.168.134.129
将MySQL加入到环境变量中：
[root@slave mysql]# vim /etc/profile.d/mypath.sh
export PATH=$PATH:/usr/local/mysql/bin/
[root@slave mysql]# source /etc/profile.d/mypath.sh
编辑配置文件：
[root@slave mysql]# vim /etc/my.cnf
保证server-id不与主的相同即可：
server-id = 2
（主的server-id = 1）
重启：
[root@slave mysql]# /etc/init.d/mysqld restart
Shutting down MySQL..... SUCCESS!
Starting MySQL. SUCCESS!
在从上也创建库db1，
[root@slave mysql]# mysql -e "create database db1"
先将主上备份的123.sql拷贝到从上/usr/local/mysql目录下：
[root@slave mysql]# scp root@192.168.134.128:/usr/local/mysql/123.sql /usr/local/mysql/123.sql
The authenticity of host '192.168.134.128 (192.168.134.128)' can't be established.
RSA key fingerprint is 7d:f3:cc:4e:ae:cb:3c:31:61:d5:13:8e:04:dc:73:02.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.134.128' (RSA) to the list of known hosts.
root@192.168.134.128's password:
123.sql
将123.sql恢复到db1：
[root@slave mysql]# mysql db1 < 123.sql
保证主从上的数据库一样：
登录mysql先停掉slave：
[root@slave mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.73 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> slave stop;
Query OK, 0 rows affected, 1 warning (0.00 sec)
配置主从关系（非常关键）：
mysql> change master to master_host='192.168.134.128',master_port=3306,master_user='slave',master_password='123abc',master_log_file='mysql-bin.000001',master_log_pos=338;
Query OK, 0 rows affected (0.42 sec)
开启slave：
mysql> slave start;
Query OK, 0 rows affected (0.00 sec)
查看slave状态，显示两个Yes即为配置成功：
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.134.128
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 338
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000001
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: 338
Relay_Log_Space: 406
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
4.测试MySQL主从：MySQL主从不可以在从上操作，一旦在从上执行一些写入操作的话，主从机制会发生紊乱。
测试1：在主上删除一个表，从上也会删除：
主：192.168.134.128
[root@master mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.73-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
解锁表：
mysql> unlock tables;
Query OK, 0 rows affected (0.01 sec)
使用db1：
mysql> use db1;
Database changed
查看表：
mysql> show tables;
+---------------------------+
| Tables_in_db1 |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
23 rows in set (0.00 sec)
删除表：
mysql> drop table help_category ;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------------------+
| Tables_in_db1 |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
22 rows in set (0.00 sec)
从：192.168.134.129
[root@slave mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.73 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
使用db1：
mysql> use db1;
Database changed
查看表：
mysql> show tables;
+---------------------------+
| Tables_in_db1 |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
22 rows in set (0.00 sec)
可以看到从上help_category 也被删除了。
测试2：在主上创建一个表，从上也会创建：
主：192.168.134.128
创建表：
mysql> create table tb1 (`id` int(4),`name` char(40)) ENGINE=MyISAM DEFAULT CHARSET=gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------------------+
| Tables_in_db1 |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| tb1 |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
23 rows in set (0.00 sec)
从：192.168.134.129
mysql> show tables;
+---------------------------+
| Tables_in_db1 |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| tb1 |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
23 rows in set (0.00 sec)
可以看到刚创建的表。
测试3：在主上删除库，从上也不能再使用
主：192.168.134.128
删除库：
mysql> drop database db1;
Query OK, 23 rows affected (0.01 sec)
mysql> show tables;
ERROR 1046 (3D000): No database selected
查看库：mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
从：192.168.134.129
mysql> show tables;
ERROR 1049 (42000): Unknown database 'db1'
报错：Unknown database 'db1'
查看库：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
可以看到也没有db1库了。