2. 创建数据库

创建目录并修改权限:

    [root@mysqldb2 local]# cd /data

    [root@mysqldb2 data]# mkdir mysqldata

    [root@mysqldb2 data]# cd mysqldata

    [root@mysqldb2 mysqldata]# mkdir 3306

    [root@mysqldb2 mysqldata]# cd 3306

    [root@mysqldb2 3306]# mkdir data binlog tmp innodb_ts innodb_log

    [root@mysqldb2 3306]# cd /data

    [root@mysqldb2 data]# chown -R mysql:mysql mysqldata

创建数据库:

    [root@mysqldb2 local]# cd mysql/

    [root@mysqldb2 mysql]# bin/mysql_install_db --user=mysql --datadir=/data/mysqldata/3306/data

    Installing MySQL system tables...

    OK

    Filling help tables...

    OK

    To start mysqld at boot time you have to copy

    support-files/mysql.server to the right place for your system

    PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

    To do so, start the server, then issue the following commands:

    /usr/local/mysql/bin/mysqladmin -u root password ¨new-password¨

    /usr/local/mysql/bin/mysqladmin -u root -h mysqldb2 password ¨new-password¨

    Alternatively you can run:

    /usr/local/mysql/bin/mysql_secure_installation

    which will also give you the option of removing the test

    databases and anonymous user created by default.  This is

    strongly recommended for production servers.

    See the manual for more instructions.

    You can start the MySQL daemon with:

    cd /usr/local/mysql ; /usr/local/mysql/bin/mysqld_safe &

    You can test the MySQL daemon with mysql-test-run.pl

    cd /usr/local/mysql/mysql-test ; perl mysql-test-run.pl

    Please report any problems with the /usr/local/mysql/bin/mysqlbug script!

编辑和修改my.cnf配置文件,下列内容仅供参考:

    [root@mysqldb2 mysql]# vi /data/mysqldata/3306/my.cnf

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

    # Here follows entries for some specific programs

    # The MySQL server
    [mysqld]
    port = 3306
    user = mysql
    socket = /data/mysqldata/3306/mysql.sock
    pid-file = /data/mysqldata/3306/mysql.pid
    basedir = /usr/local/mysql
    datadir = /data/mysqldata/3306/data
    tmpdir = /data/mysqldata/3306/tmp
    open_files_limit = 10240
    server-id = 303306
    lower_case_table_names = 1
    character-set-server = utf8
    skip-name-resolve

    max_connections = 1000
    max_connect_errors = 100000
    max_allowed_packet = 512M
    max_heap_table_size = 1024M
    max_length_for_sort_data = 4096
    back_log=100
    interactive_timeout = 600
    wait_timeout = 600

    default-storage-engine = InnoDB

    net_buffer_length = 8K
    sort_buffer_size = 2M
    join_buffer_size = 4M
    read_buffer_size = 2M
    read_rnd_buffer_size = 16M

    query_cache_size = 128M
    query_cache_limit = 2M
    query_cache_min_res_unit = 2k

    thread_cache_size = 300
    table_open_cache = 1024
    tmp_table_size = 256M

    #*********** Logs related settings ***********
    log-bin = /data/mysqldata/3306/binlog/mysql-bin
    binlog_format=row
    binlog_cache_size=32m
    max_binlog_cache_size=512m
    max_binlog_size=512m
    long_query_time = 3
    log_output = FILE
    log-error = /data/mysqldata/3306/mysql-error.log
    slow_query_log = 1
    slow_query_log_file = /data/mysqldata/3306/slow_statement.log
    log_queries_not_using_indexes
    general_log = 0
    general_log_file = /data/mysqldata/3306/general_statement.log
    expire-logs-days = 14

    #*********** MyISAM Specific options ***********
    key_buffer_size = 32M
    bulk_insert_buffer_size = 64M
    myisam_sort_buffer_size = 128M
    myisam_max_sort_file_size = 10G
    myisam_repair_threads = 1
    myisam_recover

    #*********** INNODB Specific options ***********
    innodb_file_per_table
    transaction-isolation = READ-COMMITTED

    innodb_additional_mem_pool_size = 16M
    innodb_buffer_pool_size = 1024M
    innodb_data_home_dir = /data/mysqldata/3306/innodb_ts
    innodb_data_file_path = ibdata1:2048M:autoextend

    innodb_file_io_threads = 4
    innodb_thread_concurrency = 0
    innodb_log_buffer_size = 16M
    innodb_log_file_size = 256M
    innodb_log_files_in_group = 2

    innodb_log_group_home_dir = /data/mysqldata/3306/innodb_log
    innodb_flush_log_at_trx_commit = 2
    innodb_max_dirty_pages_pct = 80
    innodb_lock_wait_timeout = 120
    innodb_flush_method=O_DIRECT

    [mysqldump]
    quick
    max_allowed_packet = 512M

    [mysql]
    no-auto-rehash
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates

    [myisamchk]
    key_buffer_size = 32M
    sort_buffer_size = 20M
    read_buffer_size = 2M
    write_buffer_size = 2M

    [mysqlhotcopy]
    interactive-timeout

    [mysqld_safe]
    open-files-limit = 8192

启动数据库:

    [root@mysqldb2 mysql]# mysqld_safe --defaults-file=/data/mysqldata/3306/my.cnf &

设置超级用户密码:

    [root@mysqldb2 mysql]# mysqladmin -uroot password ¨verysafe¨ -S /data/mysqldata/3306/mysql.sock 

可以通过netstat或ps等命令查看mysqld是否正常加载,由于默认指定mysqld端口号为3306,因此通过netstat查看是否存在3306端口即可,例如:

    [root@mysqldb2 mysql]# netstat -lnt

    Active Internet connections (only servers)

    Proto Recv-Q Send-Q Local Address               Foreign Address             State      

    tcp        0      0 127.0.0.1:2208              0.0.0.0:*                   LISTEN      

    tcp        0      0 0.0.0.0:864                 0.0.0.0:*                   LISTEN      

    tcp        0      0 0.0.0.0:11111               0.0.0.0:*                   LISTEN      

    tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      

    tcp        0      0 0.0.0.0:111                 0.0.0.0:*                   LISTEN      

    tcp        0      0 0.0.0.0:16851               0.0.0.0:*                   LISTEN      

            0      0 0.0.0.0:21                  0.0.0.0:*                   LISTEN