学习日记6：mysql主从设置及测试
本次完成的是架构图最下面的2个db ：master - slave 结构的设计。
mysql 我是用yum装的，但是看过一些相关文章，建议还是采用编译安装。可以指定相关参数，如：
关键字 : mysql compile
1. -static 13%
--with-client-ldflags=-all-static
--with-mysqld-ldflags=-all-static
静态链接提高13%性能
2. -pgcc 1%
CFLAGS="-O3 -mpentiumpro -mstack-align-double" CXX=gcc \
CXXFLAGS="-O3 -mpentiumpro -mstack-align-double \
-felide-constructors -fno-exceptions -fno-rtti"
如果是Inter处理器，使用pgcc提高1%性能
3. Unix Socket 7.5%
--with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock
使用unix套接字链接提高7.5%性能，所以在windows下mysql性能肯定不如unix下面
4. --enable-assembler
允许使用汇编模式(优化性能)
下面是总体的编译文件
编译代码CFLAGS="-O3" CXX=gcc CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti -fomit-frame-pointer -ffixed-ebp"./configure --prefix=/usr/local/mysql --enable-assembler --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock --with-charset=utf8 --with-collation=utf8_general_ci --with-extra-charsets=all -prefix=/data/app/mysql5123 --datadir=/data/mysqldata --sysconfdir=/data/app/mysql5123/etc --with-charset=utf8 --enable-assembler --without-isam --with-pthread --enable-thread-safe-client --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --with-extra-charsets=all --with-unix-socket-path=/data/app/mysql5123/tmp/mysql.sock
1）
在这里，我的master db 是192.168.65.131
slave db 是192.168.65.132
且都关闭防火墙
2 配置master 首先编辑/etc/my.cnf，添加以下配置：
log-bin=mysql-bin #slave会基于此log-bin来做replication server-id=131 #master的标示 binlog-do-db = amoeba_study #用于master-slave的具体数据库
然后添加专门用于replication的用户：
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@192.168.65.132 IDENTIFIED BY '123456';
重启mysql，使得配置生效：
/etc/init.d/mysqld restart
最后查看master状态：
mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 107 | test | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
3）配置slave 首先编辑/etc/my.cnf，添加以下配置：
server-id=132 #slave的标示
配置生效后，
配置与master的连接：
mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.65.131', -> MASTER_USER='repl', -> MASTER_PASSWORD='123456', -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=107;
其中MASTER_HOST是master机的ip，MASTER_USER和MASTER_PASSWORD就是我们刚才在master上添加的用户，MASTER_LOG_FILE和MASTER_LOG_POS对应与master status里的信息
最后启动slave：
mysql> start slave;
4）验证master-slave搭建生效 通过查看slave机的log（/var/log/mysqld.log）：
100703 10:51:42 [Note] Slave I/O thread: connected to master 'repl@192.168.65.131:3306', replication started in log 'mysql-bin.000001' at position 107
如看到以上信息则证明搭建成功，如果有问题也可通过此log找原因
在从库上查看下状态：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.65.131
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes //必须是yes
Slave_SQL_Running: Yes //必须是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: 107
Relay_Log_Space: 410
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 131
1 row in set (0.01 sec)
注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。 以上操作过程，主从服务器配置完成。
我们去主db上看下，a 新建个表，b 插入条数据；
mysql> use test; Database changed mysql> create table hi_tb(id int(3),name char(10)); Query OK, 0 rows affected (0.11 sec) mysql> insert into hi_tb values(001,'bobu'); Query OK, 1 row affected (0.02 sec)
我们在从库看下，a 当主库建完表后，看下show；b 插入数据后，再select下；
mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | hi_tb | +----------------+ 1 row in set (0.00 sec) mysql> select * from hi_tb; Empty set (0.00 sec) mysql> select * from hi_tb; +------+------+ | id | name | +------+------+ | 1 | bobu | +------+------+ 1 row in set (0.00 sec)
ok 一切都是完美的！！！
