设为首页收藏本站
查看: 83|回复: 0

[PHP] Linux平台使用二进制包将MySQL5.1安装到个人目录简易指南

[复制链接]

论坛元老

Rank: 6Rank: 6

积分
34274
主题
17031
UID
1347
M币
67
贡献
17176

  • 发表于 2018-7-3 17:48:00 | 显示全部楼层 |阅读模式
    Linux平台使用二进制包将MySQL 5.1安装到个人目录简易指南

      1. 解压二进制包到个人目录,例如/home/liyh/local/mysql;
    2. 拷贝一份系统目录的my.cnf配置(或者后面的配置样例也可以),按照如下方式修改:
    2.1 将其中所有目录相关的配置全部改为个人目录,且要用绝对路径,不能用$HOME. 例如data, log(注意二进制包解压后缺省并没有log、tmp目录,需要自己手工创建),为了提高性能,建议数据目录和日志目录分开到不同的盘不同的目录,例如:
    datadir = /home1/liyh/mysql/data/
    log-error = /home/liyh/local/mysql/log/mysqld.err
    log-bin=/home/liyh/local/mysql/log/mysql-bin
    innodb_data_home_dir = /home1/liyh/mysql/data
    2.2 修改[mysqld]下的port为你自己的端口;
    2.3 修改[mysqld]下的user为你的用户名;
    3. 将mysql的目录权限修改为你的用户,例如:
    chgrp -R liyh mysql;
    chown -R liyh mysql;
    chmod 775 mysql;
    4. 到mysql目录下的scripts目录,执行如下语句:
    ./mysql_install_db --defaults-file=/home/liyh/local/mysql/my.cnf --user=liyh --basedir=/home/liyh/local/mysql
    5. 执行如下语句启动:
    /home/liyh/local/mysql/bin/mysqld_safe --defaults-file=/home/liyh/local/mysql/my.cnf --basedir=/home/liyh/local/mysql &

    【特别注意】为了提高InnoDB引擎的性能,记得一定要按照如下方式修改两个参数,否则你的MySQL insert和update慢得如蜗牛,但高性能同时也是有损失的,这样配置会降低MySQL的可靠性,某些场景下可能丢失数据,实际应用需要你自己评估是否要这样配置:
    sync_binlog=0 1代表每次执行一条insert/update/delete都同步一下磁盘,0代表由操作系统决定何时同步磁盘,大于0代表达到多少条后再同步磁盘,如果可靠性要求较低,建议设置为0或者100以上
    innodb_flush_log_at_trx_commit = 2 默认为1,意思是每次事务提交时都同步日志到磁盘,0代表每秒同步一次,事务提交时不同步,2代表只将事务日志写入缓存,每秒由系统同步磁盘;
    ==================配置样例如下====================================
    [client]
    port = 19816
    socket = /home/liyh/local/mysql/tmp/mysql.sock
    default-character-set = utf8
    [mysqld]
    character-set-server = utf8
    port = 19816
    socket = /home/liyh/local/mysql/tmp/mysql.sock
    user = liyh
    skip-external-locking
    datadir = /home1/liyh/mysql/data/
    log-error = /home/liyh/local/mysql/log/mysqld.err
    pid-file = /home/liyh/local/mysql/tmp/mysql.pid
    #autocommit = off
    #read-only
    key_buffer_size = 64M
    myisam_sort_buffer_size = 300M
    max_allowed_packet = 16M
    table_open_cache = 4096
    sort_buffer_size = 5M
    join_buffer_size = 5M
    read_buffer_size = 5M
    read_rnd_buffer_size = 8M
    bulk_insert_buffer_size = 64M
    query_cache_size = 8M
    query_cache_limit=8M
    query_cache_min_res_unit = 2k
    tmp_table_size=64M
    max_heap_table_size = 128M
    open_files_limit=102400
    thread_cOncurrency= 16
    max_cOnnections=3000
    max_connect_errors=3000
    back_log = 600
    table_cache = 4096
    thread_cache_size = 100
    thread_stack = 192K
    #transaction_isolation = READ-COMMITTED
    skip-name-resolve
    ####log variables
    server-id = 1
    binlog_format=mixed
    #log-slave-updates
    log-bin=/home/liyh/local/mysql/log/mysql-bin
    relay_log=/home/liyh/local/mysql/log/relay-bin
    max_binlog_size = 500M
    binlog_cache_size = 1M
    max_binlog_cache_size = 64M
    #binlog-ignore-db = mysql
    replicate-ignore-db = mysql
    expire-logs-days = 10
    sync_binlog=0
    slow_query_log=1
    slow_query_log_file=/home/liyh/local/mysql/log/slowquery.log
    long_query_time=1
    general_log=off
    general_log_file=/home/liyh/local/mysql/log/general.log
    #relay_log_purge=OFF
    # Point the following paths to different dedicated disks
    #tmpdir = /tmp/
    #log-update = /path-to-dedicated-directory/hostname
    #auto_increment_increment=1
    #auto_increment_offset=1
    ####innodb variables
    #skip-innodb
    innodb_data_home_dir = /home1/liyh/mysql/data
    innodb_data_file_path = ibdata1:2000M;ibdata2:100M:autoextend
    innodb_log_group_home_dir = /home1/liyh/mysql/data
    #innodb_log_arch_dir = /home/liyh/local/mysql/data/
    innodb_buffer_pool_size = 2048M
    innodb_additional_mem_pool_size = 64M
    innodb_log_file_size = 200M
    innodb_log_buffer_size = 8M
    innodb_log_files_in_group = 3
    innodb_flush_log_at_trx_commit = 2
    #innodb_file_io_threads = 4
    innodb_thread_cOncurrency= 16
    default-storage-engine=innodb
    #innodb_max_dirty_pages_pct = 90
    #innodb_lock_wait_timeout = 60
    #innodb_file_per_table = 0
    #interactive_timeout = 600
    #wait_timeout = 900
    [mysqldump]
    quick
    max_allowed_packet = 16M
    [mysql]
    no-auto-rehash
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates
    [myisamchk]
    key_buffer_size = 512M
    sort_buffer_size = 512M
    read_buffer = 5M
    write_buffer = 5M
    [mysqlhotcopy]
    interactive-timeout

    回复

    使用道具 举报

    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    在我站开通SVIP可同时获得17个站点VIP资源 立即登录 立即注册
    快速回复 返回顶部 返回列表