mysql 主从/优化

编译优化 ：

CFLAGS="-O3 -mpentiumpro -mstack-align-double"
CXX=gcc
CXXFLAGS="-O3 -mpentiumpro -mstack-align-double -felide-constructors -fno-exceptions -fno-rtti"

编译：
./configure --prefix=/usr/local/mysql --enable-assembler\
        --with-innodb  \
        --with-charset=utf8 \
        --enable-thread-safe-client  \
        --with-collation=utf8_general_ci \
        --with-mysqld-ldflags=-all-static  \
        --with-client-ldflags=-all-static  \
        --with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock  \
        --with-collation=utf8_general_ci  \
        --with-extra-charsets=all  \

make && make install

创建 co_mysql 用户
  groupadd mysql
  adduser xxx --home-dir=/dev/null  --groups mysql
  chown -R xxx:mysql mysql_data_xxx
  mysql_install_db --datadir=/data/mysql_data_youyanban/data





创建使用目录


|-- READER #
|-- bin
| |-- mysql #登入 ./bin/mysql -uroot -pmysql
| |-- start #服务器启动
| | # 详细配置查看 conf/my-innodb.cof
| `-- stop #关闭 （ 主服务器重启，不会影响同步.已 经测试 ）
|
|-- conf
| `-- my-innobd.cof #服务配置
|
|-- data # 数据 存储地址
| |-- mysql
| `-- test
|
|-- log #日志存放
| |-- mysql.err # 运行日志 可以 tail -f ....
|
|-- tmp
| `-- mysql.sock
|
|-- dak #备份
`-- tools


详细文件说明

#文件 mysql 
#!/bin/sh
/usr/local/mysql5/bin/mysql   --defaults-file=/data/db_data/mysql_data/conf/my.cnf $1 $2 $3 $4 $5 $6 $7 $8 $9 


#文件 mysql-start
#!/bin/sh
/usr/local/mysql5/bin/mysqld_safe  --defaults-file=/data/db_data/mysql_data/conf/my.cnf


#文件 mysql-stop
#!/bin/sh
/usr/local/mysql5/bin/mysqladmin  --defaults-file=/data/db_data/mysql_data/conf/my.cnf  -uroot -pmysql shutdown  

(主)数据库 配置文件 :
 

170  mysql  主从

my.cnf 配置 master

[client]
port = 3306
socket = /data/..../tmp/mysql.sock
default-character-set=utf8

[mysqld]
server-id=1
port=3306
socket=/data/..../tmp/mysql.sock
log-bin=/data/.../log/mysql-bin




#需要同步 数据库
binlog-do-db=points
binlog-do-db=poweruser
binlog-do-db=usercore
binlog-do-db=test


#不需要同步数据库
binlog-ignore-db=mysql


#sql-bin-update-same

slow_query_log
slow_query_log_file=/data/..../log/slow_query.log

skip-locking
init_connect='SET NAMES utf8'
open_files_limit=4096
back_log = 50

max_connections = 2000
max_connect_errors = 100
table_open_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer_size = 4096M
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 1024M
query_cache_limit = 2M
ft_min_word_len = 4

default-storage-engine = MYISAM
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
binlog_format=mixed
log_warnings
long_query_time = 2
tmpdir = /data/.../tmp
key_buffer_size = 256M
read_buffer_size = 1024M
read_rnd_buffer_size = 1024M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 4096M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover

innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120


[mysqldump]
quick
max_allowed_packet = 16M
socket=/data/.../tmp/mysql.sock

[mysql]
no-auto-rehash
socket=/data/.../tmp/mysql.sock
                                                     
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 8192
datadir=/data/.../data/
socket=/data/.../tmp/mysql.sock
log-error=/data/.../log/mysql_error


[mysqladmin]
socket=/data/.../tmp/mysql.sock

从数据库优化

# 主服务器 配置可以拷贝 账户
GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.*
TO backup@'%'
IDENTIFIED BY 'pu.backup'

.......

[mysqld]
server-id=2
port=3307
socket=/data/..._slaver/tmp/mysql.sock
log-bin=/data/..._slaver/log/mysql-bin

master-host=127.0.0.1
master-user    = backup
master-password    = ...