mysql --enable-assembler_学习日记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 一切都是完美的！！！