MYSQL 5.5安装与多实例运行

2eew

使用Mysql前，需安装一个工具和两个依赖包，yum install cmake ncurses ncurses-devel -y
Mysql 5.5 安装说明可以参考http://dev.mysql.com/doc/refman/5.5/en/source-installation.html

从官网下载源码安装包
wget http://cdn.mysql.com//Downloads/MySQL-5.5/mysql-5.5.47.tar.gz
解压，tar -zxf mysql-5.5.47.tar.gz ，得到文件夹mysql-5.5.47，建议将该文件夹移入到/usr/local/src或自己找个地方专门放源文件的文件夹，我的测试机是/disk/src
因此

1	mv mysql-5.5.47 /disk2/src; cd /disk2/src;

执行，留意路径和版本号的修改

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

cmake . -DCMAKE_INSTALL_PREFIX=/disk2/mysql-5.5.47 \ -DMYSQL_DATADIR=/disk2/mysql-5.5.47/data \ -DMYSQL_UNIX_ADDR=/disk2/mysql-5.5.47/tmp/mysql.sock \ -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci \ -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \ -DENABLED_LOCAL_INFILE=ON \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_FEDERATED_STORAGE_ENGINE=1 \ -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \ -DWITHOUT_PARTITION_STORAGE_ENGINE=1 \ -DWITH_FAST_MUTEXES=1 \ -DWITH_ZLIB=bundled \ -DENABLED_LOCAL_INFILE=1 \ -DWITH_READLINE=1 \ -DWITH_EMBEDDED_SERVER=1 \ -DWITH_DEBUG=0

如果成功，会提示
-- Configuring done
-- Generating done
-- Build files have been written to: /disk2/src/mysql-5.5.47

然后执行

1 2 3 4 5 6 7 8 9 10 11 12 13 14

[iyunv@vmtest mysql-5.5.47]# make && make install Scanning dependencies of target INFO_BIN [  0%] Built target INFO_BIN Scanning dependencies of target INFO_SRC .... .... Scanning dependencies of target gen_lex_hash [ 68%] Building CXX object sql/CMakeFiles/gen_lex_hash.dir/gen_lex_hash.cc.o Linking CXX executable gen_lex_hash [ 68%] Built target gen_lex_hash Scanning dependencies of target GenServerSource [ 68%] Generating lex_hash.h [ 68%] Built target GenServerSource .....

创建软连接

1	ln -s /disk2/mysql-5.5.47 mysql

配置全局变量

1 2	echo 'export PATH=/disk2/mysql-5.5.47/bin:$PATH' >> /etc/profile . /etc/profile

Mysql的本体程序到此基本完成，接下来是配置多实例

创建自己的多实例文件夹

1 2 3 4 5 6 7 8 9

mkdir -p /disk2/mysql_multi_instances/{3306..3308}/data tree /disk2/mysql_multi_instances /disk2/mysql_multi_instances ├── 3306 │   └── data ├── 3307 │   └── data └── 3308     └── data

实例的配置文件将放于端口文件夹下，而数据库文件路径则在data下，还是那句，根据自己的喜好决定即可。

创建实例的配置文件，除了注意修改port和路径外，还需注意修改server-id，每个实例的server-id 必须不同。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75

vi /disk2/mysql_multi_instances/3306/my.cnf [client] port            = 3306 socket          = /disk2/mysql_multi_instances/3306/mysql.sock [mysql] no-auto-rehash [mysqld] user    = mysql port    = 3306 socket  = /disk2/mysql_multi_instances/3306/mysql.sock basedir = /disk2/mysql datadir = /disk2/mysql_multi_instances/3306/data open_files_limit    = 1024 back_log = 600 max_connections = 800 max_connect_errors = 3000 table_cache = 614 external-locking = FALSE max_allowed_packet =8M sort_buffer_size = 1M join_buffer_size = 1M thread_cache_size = 100 thread_concurrency = 2 query_cache_size = 2M query_cache_limit = 1M query_cache_min_res_unit = 2k #default_table_type = InnoDB thread_stack = 192K #transaction_isolation = READ-COMMITTED tmp_table_size = 2M max_heap_table_size = 2M long_query_time = 1 #log_long_format #log-error = /disk2/mysql_multi_instances/3306/error.log #log-slow-queries = /disk2/mysql_multi_instances/3306/slow.log pid-file = /disk2/mysql_multi_instances/3306/mysql.pid log-bin = /disk2/mysql_multi_instances/3306/mysql-bin relay-log = /disk2/mysql_multi_instances/3306/relay-bin relay-log-info-file = /disk2/mysql_multi_instances/3306/relay-log.info binlog_cache_size = 1M max_binlog_cache_size = 1M max_binlog_size = 2M expire_logs_days = 7 key_buffer_size = 16M read_buffer_size = 1M read_rnd_buffer_size = 1M bulk_insert_buffer_size = 1M #myisam_sort_buffer_size = 1M #myisam_max_sort_file_size = 10G #myisam_max_extra_sort_file_size = 10G #myisam_repair_threads = 1 #myisam_recover lower_case_table_names = 1 skip-name-resolve slave-skip-errors = 1032,1062 replicate-ignore-db=mysql server-id = 1 innodb_additional_mem_pool_size = 4M innodb_buffer_pool_size = 32M innodb_data_file_path = ibdata1:128M:autoextend innodb_file_io_threads = 4 innodb_thread_concurrency = 8 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2M innodb_log_file_size = 4M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 innodb_file_per_table = 0 [mysqldump] quick max_allowed_packet = 2M [mysqld_safe] log-error=/disk2/mysql_multi_instances/3306/mysql_3306.err pid-file=/disk2/mysql_multi_instances/3306/mysqld.pid

保存后，修改文件的所有者。

1 2	useradd -M -s /sbin/nologin mysql chown -R mysql.mysql /disk2/mysql_multi_instances

尝试运行mysqld服务器进程，但报错了。

1 2 3 4

mysqld_safe --defaults-file=/disk2/mysql_multi_instances/3306/my.cnf 160115 11:40:59 mysqld_safe Logging to '/disk2/mysql_multi_instances/3306/mysql_3306.err'. 160115 11:41:00 mysqld_safe Starting mysqld daemon with databases from /disk2/mysql_multi_instances/3306/data 160115 11:41:01 mysqld_safe mysqld from pid file /disk2/mysql_multi_instances/3306/mysqld.pid ended

经查原来没有初始化数据库

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

/disk2/mysql-5.5.47/scripts/mysql_install_db --basedir=/disk2/mysql --datadir=/disk2/mysql_multi_instances/3306/data --user=mysql Installing MySQL system tables... 160115 11:48:39 [Note] /disk2/mysql/bin/mysqld (mysqld 5.5.47) starting as process 26793 ... OK Filling help tables... 160115 11:48:39 [Note] /disk2/mysql/bin/mysqld (mysqld 5.5.47) starting as process 26800 ... 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: /disk2/mysql/bin/mysqladmin -u root password 'new-password' /disk2/mysql/bin/mysqladmin -u root -h vmtest.js password 'new-password' Alternatively you can run: /disk2/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 /disk2/mysql ; /disk2/mysql/bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd /disk2/mysql/mysql-test ; perl mysql-test-run.pl Please report any problems at http://bugs.mysql.com/

再次运行

1 2 3

mysqld_safe --defaults-file=/disk2/mysql_multi_instances/3306/my.cnf 160115 11:49:45 mysqld_safe Logging to '/disk2/mysql_multi_instances/3306/mysql_3306.err'. 160115 11:49:45 mysqld_safe Starting mysqld daemon with databases from /disk2/mysql_multi_instances/3306/data

会一直停留则代表进程运行成功，那么下一次运行该实例时，只需要改为如下方式即可

1	mysqld_safe --defaults-file=/disk2/mysql_multi_instances/3306/my.cnf  &>/dev/null &

同理，先初始化3307文件夹里的数据库文件，然后复制并调整my.cnf文件并复制到3307下，再执行mysqld_safe语句即可启动第二个实例。这里就不贴出来了。

退出该实例，注意执行的权限。

1	mysqladmin -S /disk2/mysql_multi_instances/3306/mysql.sock shutdown

数据库运行起来了，但还缺管理员的配置，或者应该说当前的配置不安全，因为mysql的默认管理员root的密码是空的。注意这个mysql管理员root跟linux系统的root其实没什么关系的。
初始化密码是

1	mysqladmin -uroot -S /disk2/mysql_multi_instances/3306/mysql.sock password 'cooshpassword';

以后要修改密码则是

1	mysqladmin -uroot -pcooshpassword -S /disk2/mysql_multi_instances/3306/mysql.sock password 'cooshnewpassword';

当管理员有密码后，关闭数据库则可以用以下方式

1	mysqladmin -uroot -pcooshnewpassword -S /disk2/mysql_multi_instances/3306/mysql.sock shutdown

设好密码后，登录数据库的方式
使用socket文件

1	mysql -uroot -pjet-speed -S /disk2/mysql_multi_instances/3306/mysql.sock