建立MySQL账号
[root@localhost ~]#groupadd mysql
[root@localhost ~]#useradd -s /sbin/nologin -g mysql -M mysql#-s /sbin/nologin 禁止该用户登陆#-g mysql 指定属于mysql组#-M 表示不创建用户家目录
安装mysql软件
环境部署
mysql-5.6.22-linux-glibc2.5-x86_64.tar.gz CentOS6.9
[root@localhost ~]#mkdir /home/software
[root@localhost ~]#cd /home/software/
[root@localhost software]#ls
mysql-5.6.22-linux-glibc2.5-x86_64.tar.gz
解压和迁移
[root@localhost software]#ls
mysql-5.6.22-linux-glibc2.5-x86_64 mysql-5.6.22-linux-glibc2.5-x86_64.tar.gz
[root@localhost software]#mv mysql-5.6.22-linux-glibc2.5-x86_64 /usr/local/mysql
[root@localhost software]#cd /usr/local/mysql/
[root@localhost mysql]#ls
bin COPYING data docs include INSTALL-BINARY lib man mysql-test README scripts share sql-bench support-files
创建相关目录
[root@localhost ~]#mkdir -p /data/mysql/{mysql_3306,mysql_3307}
[root@localhost ~]#mkdir /data/mysql/mysql_3306/{data,log,tmp}
[root@localhost ~]#mkdir /data/mysql/mysql_3307/{data,log,tmp}
更改目录权限
[root@localhost ~]#chown -R mysql:mysql /data/mysql/
[root@localhost ~]#chown -R mysql:mysql /usr/local/mysql/
添加环境变量
[root@localhost ~]#echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile
[root@localhost ~]#source /etc/profile
复制my.cnf文件到etc目录
[root@localhost ~]#cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
cp: overwrite `/etc/my.cnf'? y
修改my.cnf
[root@localhost ~]#vi /etc/my.cnf
[root@localhost ~]#
#For advice on how to change settings please see#http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html#*** DO NOT EDIT THIS FILE. It's a template which will be copied to the#*** default location during install, and will be replaced if you#*** upgrade to a newer version of MySQL.
[client]
port=3306socket=/tmp/mysql.sock
[mysqld_multi]
mysqld= /usr/local/mysql /bin/mysqld_safe
mysqladmin= /usr/local/mysql /bin/mysqladmin
log= /data/mysql/mysqld_multi.log
[mysqld]
user=mysql
basedir= /usr/local/mysql
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
explicit_defaults_for_timestamp=true[mysqld3306]
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/data/mysql/mysql_3306/data
port=3306server_id=3306socket=/tmp/mysql_3306.sock
log-output=file
slow_query_log= 1long_query_time= 1slow_query_log_file= /data/mysql/mysql_3306/log/slow.log
log-error = /data/mysql/mysql_3306/log/error.log
binlog_format=mixed
log-bin = /data/mysql/mysql_3306/log/mysql3306_bin
[mysqld3307]
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/data/mysql/mysql_3307/data
port=3307server_id=3307socket=/tmp/mysql_3307.sock
log-output=file
slow_query_log= 1long_query_time= 1slow_query_log_file= /data/mysql/mysql_3307/log/slow.log
log-error = /data/mysql/mysql_3307/log/error.log
binlog_format=mixed
log-bin = /data/mysql/mysql_3307/log/mysql3307_bin#Remove leading # and set to the amount of RAM for the most important data#cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.#innodb_buffer_pool_size = 128M
#Remove leading # to turn on a very important data integrity option: logging#changes to the binary log between backups.#log_bin
#These are commonly set, remove the # and set as required.#basedir = .....#datadir = .....#port = .....#server_id = .....#socket = .....
#Remove leading # to set options mainly useful for reporting servers.#The server defaults are faster for transactions and fast SELECTs.#Adjust sizes as needed, experiment to find the optimal values.#join_buffer_size = 128M#sort_buffer_size = 2M#read_rnd_buffer_size = 2M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
my.cnf
初始化数据库
初始化3306数据库
[root@localhost ~]#/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3306/data --defaults-file=/etc/my.cnf
初始化后显示
[root@localhost ~]#/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3306/data --defaults-file=/etc/my.cnf
Installing MySQL system tables...2018-11-27 00:36:44 3144[Note] InnoDB: Using atomics to ref count buffer pool pages2018-11-27 00:36:44 3144[Note] InnoDB: The InnoDB memory heap is disabled2018-11-27 00:36:44 3144[Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins2018-11-27 00:36:44 3144[Note] InnoDB: Memory barrier is not used2018-11-27 00:36:44 3144 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-11-27 00:36:44 3144[Note] InnoDB: Using Linux native AIO2018-11-27 00:36:44 3144[Note] InnoDB: Using CPU crc32 instructions2018-11-27 00:36:44 3144 [Note] InnoDB: Initializing buffer pool, size = 128.0M2018-11-27 00:36:45 3144[Note] InnoDB: Completed initialization of buffer pool2018-11-27 00:36:45 3144 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2018-11-27 00:36:45 3144 [Note] InnoDB: Setting file ./ibdata1 size to 12MB2018-11-27 00:36:45 3144[Note] InnoDB: Database physically writes the file full: wait...2018-11-27 00:36:45 3144 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48MB2018-11-27 00:36:45 3144 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48MB2018-11-27 00:36:46 3144 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile02018-11-27 00:36:46 3144 [Warning] InnoDB: New log files created, LSN=45781
2018-11-27 00:36:46 3144[Note] InnoDB: Doublewrite buffer not found: creating new2018-11-27 00:36:46 3144[Note] InnoDB: Doublewrite buffer created2018-11-27 00:36:46 3144 [Note] InnoDB: 128rollback segment(s) are active.2018-11-27 00:36:46 3144[Warning] InnoDB: Creating foreign key constraint system tables.2018-11-27 00:36:46 3144[Note] InnoDB: Foreign key constraint system tables created2018-11-27 00:36:46 3144[Note] InnoDB: Creating tablespace and datafile system tables.2018-11-27 00:36:46 3144[Note] InnoDB: Tablespace and datafile system tables created.2018-11-27 00:36:46 3144 [Note] InnoDB: Waiting forpurge to start2018-11-27 00:36:46 3144 [Note] InnoDB: 5.6.22 started; log sequence number 0
2018-11-27 00:36:47 3144 [Note] Binlog end
2018-11-27 00:36:47 3144[Note] InnoDB: FTS optimize thread exiting.2018-11-27 00:36:47 3144[Note] InnoDB: Starting shutdown...2018-11-27 00:36:48 3144 [Note] InnoDB: Shutdown completed; log sequence number 1625977OK
Filling help tables...2018-11-27 00:36:48 3168[Note] InnoDB: Using atomics to ref count buffer pool pages2018-11-27 00:36:48 3168[Note] InnoDB: The InnoDB memory heap is disabled2018-11-27 00:36:48 3168[Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins2018-11-27 00:36:48 3168[Note] InnoDB: Memory barrier is not used2018-11-27 00:36:48 3168 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-11-27 00:36:48 3168[Note] InnoDB: Using Linux native AIO2018-11-27 00:36:48 3168[Note] InnoDB: Using CPU crc32 instructions2018-11-27 00:36:48 3168 [Note] InnoDB: Initializing buffer pool, size = 128.0M2018-11-27 00:36:48 3168[Note] InnoDB: Completed initialization of buffer pool2018-11-27 00:36:48 3168[Note] InnoDB: Highest supported file format is Barracuda.2018-11-27 00:36:48 3168 [Note] InnoDB: 128rollback segment(s) are active.2018-11-27 00:36:48 3168 [Note] InnoDB: Waiting forpurge to start2018-11-27 00:36:48 3168 [Note] InnoDB: 5.6.22 started; log sequence number 1625977
2018-11-27 00:36:48 3168 [Note] Binlog end
2018-11-27 00:36:48 3168[Note] InnoDB: FTS optimize thread exiting.2018-11-27 00:36:48 3168[Note] InnoDB: Starting shutdown...2018-11-27 00:36:50 3168 [Note] InnoDB: Shutdown completed; log sequence number 1625987OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place foryour system
PLEASE REMEMBER TO SET A PASSWORDFOR THE MySQL root USER !Todoso, 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 localhost.localdomain 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 bydefault. This is
strongly recommendedforproduction servers.
See the manualformore instructions.
You can start the MySQL daemon with:
cd . ;/usr/local/mysql//bin/mysqld_safe &You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl
Please report any problems at http://bugs.mysql.com/The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
Newdefault config file was created as /usr/local/mysql//my.cnf and
will be used bydefaultby the server when you start it.
You may edit this file to change server settings
WARNING:Default config file /etc/my.cnf exists on the system
This file will be read bydefaultby the MySQL serverIf you donot want to use this, either remove it, or use the--defaults-file argument to mysqld_safe when starting the server
View Code
初始化3307数据库
[root@localhost ~]#/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3307/data --defaults-file=/etc/my.cnf
初始化后显示
[root@localhost ~]#/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3307/data --defaults-file=/etc/my.cnf
Installing MySQL system tables...2018-11-27 00:38:40 3196[Note] InnoDB: Using atomics to ref count buffer pool pages2018-11-27 00:38:40 3196[Note] InnoDB: The InnoDB memory heap is disabled2018-11-27 00:38:40 3196[Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins2018-11-27 00:38:40 3196[Note] InnoDB: Memory barrier is not used2018-11-27 00:38:40 3196 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-11-27 00:38:40 3196[Note] InnoDB: Using Linux native AIO2018-11-27 00:38:40 3196[Note] InnoDB: Using CPU crc32 instructions2018-11-27 00:38:40 3196 [Note] InnoDB: Initializing buffer pool, size = 128.0M2018-11-27 00:38:40 3196[Note] InnoDB: Completed initialization of buffer pool2018-11-27 00:38:40 3196 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2018-11-27 00:38:40 3196 [Note] InnoDB: Setting file ./ibdata1 size to 12MB2018-11-27 00:38:40 3196[Note] InnoDB: Database physically writes the file full: wait...2018-11-27 00:38:40 3196 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48MB2018-11-27 00:38:41 3196 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48MB2018-11-27 00:38:42 3196 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile02018-11-27 00:38:42 3196 [Warning] InnoDB: New log files created, LSN=45781
2018-11-27 00:38:42 3196[Note] InnoDB: Doublewrite buffer not found: creating new2018-11-27 00:38:42 3196[Note] InnoDB: Doublewrite buffer created2018-11-27 00:38:42 3196 [Note] InnoDB: 128rollback segment(s) are active.2018-11-27 00:38:42 3196[Warning] InnoDB: Creating foreign key constraint system tables.2018-11-27 00:38:42 3196[Note] InnoDB: Foreign key constraint system tables created2018-11-27 00:38:42 3196[Note] InnoDB: Creating tablespace and datafile system tables.2018-11-27 00:38:42 3196[Note] InnoDB: Tablespace and datafile system tables created.2018-11-27 00:38:42 3196 [Note] InnoDB: Waiting forpurge to start2018-11-27 00:38:42 3196 [Note] InnoDB: 5.6.22 started; log sequence number 0
2018-11-27 00:38:43 3196 [Note] Binlog end
2018-11-27 00:38:43 3196[Note] InnoDB: FTS optimize thread exiting.2018-11-27 00:38:43 3196[Note] InnoDB: Starting shutdown...2018-11-27 00:38:44 3196 [Note] InnoDB: Shutdown completed; log sequence number 1625977OK
Filling help tables...2018-11-27 00:38:44 3218[Note] InnoDB: Using atomics to ref count buffer pool pages2018-11-27 00:38:44 3218[Note] InnoDB: The InnoDB memory heap is disabled2018-11-27 00:38:44 3218[Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins2018-11-27 00:38:44 3218[Note] InnoDB: Memory barrier is not used2018-11-27 00:38:44 3218 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-11-27 00:38:44 3218[Note] InnoDB: Using Linux native AIO2018-11-27 00:38:44 3218[Note] InnoDB: Using CPU crc32 instructions2018-11-27 00:38:44 3218 [Note] InnoDB: Initializing buffer pool, size = 128.0M2018-11-27 00:38:44 3218[Note] InnoDB: Completed initialization of buffer pool2018-11-27 00:38:44 3218[Note] InnoDB: Highest supported file format is Barracuda.2018-11-27 00:38:44 3218 [Note] InnoDB: 128rollback segment(s) are active.2018-11-27 00:38:44 3218 [Note] InnoDB: Waiting forpurge to start2018-11-27 00:38:44 3218 [Note] InnoDB: 5.6.22 started; log sequence number 1625977
2018-11-27 00:38:44 3218 [Note] Binlog end
2018-11-27 00:38:44 3218[Note] InnoDB: FTS optimize thread exiting.2018-11-27 00:38:44 3218[Note] InnoDB: Starting shutdown...2018-11-27 00:38:46 3218 [Note] InnoDB: Shutdown completed; log sequence number 1625987OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place foryour system
PLEASE REMEMBER TO SET A PASSWORDFOR THE MySQL root USER !Todoso, 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 localhost.localdomain 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 bydefault. This is
strongly recommendedforproduction servers.
See the manualformore instructions.
You can start the MySQL daemon with:
cd . ;/usr/local/mysql//bin/mysqld_safe &You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl
Please report any problems at http://bugs.mysql.com/The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
WARNING: Found existing config file/usr/local/mysql//my.cnf on the system.
Because this file might beinuse, it was not replaced,
but was usedin bootstrap (unless you used --defaults-file)
and when you later start the server.
The newdefault config file was created as /usr/local/mysql//my-new.cnf,
please compare it with your file and take the changes you need.
WARNING:Default config file /etc/my.cnf exists on the system
This file will be read bydefaultby the MySQL serverIf you donot want to use this, either remove it, or use the--defaults-file argument to mysqld_safe when starting the server
View Code
查看初始化后结果
[root@localhost ~]#cd /data/mysql/mysql_3306/data
[root@localhost data]#ls
ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema test
[root@localhost data]#cd /data/mysql/mysql_3307/data
[root@localhost data]#ls
ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema test
设置启动文件
[root@localhost data]#cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
[root@localhost data]#
mysqld_multi进行多实例管理
#启动全部实例
[root@localhost data]#/usr/local/mysql/bin/mysqld_multi start#查看全部实例状态
[root@localhost data]#/usr/local/mysql/bin/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running#启动单个实例
[root@localhost data]#/usr/local/mysql/bin/mysqld_multi start 3306#查看单个实例状态
[root@localhost data]#/usr/local/mysql/bin/mysqld_multi report 3306
Reporting MySQL servers
MySQL server from group: mysqld3306 is running#停止单个实例
[root@localhost data]#/usr/local/mysql/bin/mysqld_multi stop 3306
mysql登陆及密码修改
启动全部实例
[root@localhost tmp]#/usr/local/mysql/bin/mysqld_multi start
[root@localhost tmp]#/usr/local/mysql/bin/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
登陆修改密码
[root@localhost ~]#mysql -S /tmp/mysql_3306.sock
Welcome to the MySQL monitor. Commands endwith ; or \g.
Your MySQL connection id is2Server version:5.6.22-log MySQL Community Server (GPL)
Copyright (c)2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h'forhelp. Type '\c' to clear the current input statement.
mysql> set password for root@'localhost'=password('123456');
Query OK,0 rows affected (0.00sec)
mysql>
退出重新登陆，后面加 -p 输入密码
[root@localhost ~]#mysql -S /tmp/mysql_3306.sock -p
Enter password:
Welcome to the MySQL monitor. Commandsendwith ; or \g.
Your MySQL connection id is4Server version:5.6.22-log MySQL Community Server (GPL)
Copyright (c)2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h'forhelp. Type '\c' to clear the current input statement.
mysql>
配置运程连接
[root@localhost ~]#mysql -S /tmp/mysql_3306.sock -p
Enter password:
Welcome to the MySQL monitor. Commandsendwith ; or \g.
Your MySQL connection id is6Server version:5.6.22-log MySQL Community Server (GPL)
Copyright (c)2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h'forhelp. Type '\c' to clear the current input statement.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK,0 rows affected (0.05sec)
mysql>flush privileges;
Query OK,0 rows affected (0.05sec)
mysql>
使用Navicat连接mysql数据库
创建一个database
mysql>create database db_3306;
Query OK,1 row affected (0.00sec)
mysql>show databases;+--------------------+
| Database |
+--------------------+
| information_schema |
| db_3306 |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00sec)
mysql>
刷新Navicat可以看到已经创建成功
新增一个实例
增加一个3308实例
创建相关目录并授权
[root@localhost ~]#mkdir /data/mysql/mysql_3308
[root@localhost ~]#mkdir /data/mysql/mysql_3308/{data,log,tmp}
[root@localhost ~]#chown -R mysql:mysql /data/mysql/mysql_3308/
在my.cnf添加配置
[root@localhost ~]#vi /etc/my.cnf
添加3308配置
[mysqld3308]
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/data/mysql/mysql_3308/data
port=3308server_id=3308socket=/tmp/mysql_3308.sock
log-output=file
slow_query_log= 1long_query_time= 1slow_query_log_file= /data/mysql/mysql_3308/log/slow.log
log-error = /data/mysql/mysql_3308/log/error.log
binlog_format=mixed
log-bin = /data/mysql/mysql_3308/log/mysql3308_bin
初始化3308数据库
/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3308/data --defaults-file=/etc/my.cnf
启动3308并进行相关设置
#启动3308数据库
[root@localhost ~]#/usr/local/mysql/bin/mysqld_multi start 3308#登陆3308数据库
[root@localhost ~]#mysql -S /tmp/mysql_3308.sock#配置运程连接
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK,0 rows affected (0.00sec)#刷新权限
mysql>flush privileges;
Query OK,0 rows affected (0.00sec)#设置密码
mysql> set password for root@'localhost'=password('123456');
Query OK,0 rows affected (0.00 sec)
Navicat连接测试
查看启动的所有实例信息
[root@localhost ~]#ps -ef|grep mysql
mysql 2635 1 0 11:39 pts/0 00:00:08 mysqld --datadir=/data/mysql/mysql_3306/data --port=3306 --server_id=3306 --socket=/tmp/mysql_3306.sock --log-output=file --slow_query_log=1 --long_query_time=1 --slow_query_log_file=/data/mysql/mysql_3306/log/slow.log --log-error=/data/mysql/mysql_3306/log/error.log --binlog_format=mixed --log-bin=/data/mysql/mysql_3306/log/mysql3306_bin
mysql2702 1 0 11:48 pts/0 00:00:09 mysqld --datadir=/data/mysql/mysql_3307/data --port=3307 --server_id=3307 --socket=/tmp/mysql_3307.sock --log-output=file --slow_query_log=1 --long_query_time=1 --slow_query_log_file=/data/mysql/mysql_3307/log/slow.log --log-error=/data/mysql/mysql_3307/log/error.log --binlog_format=mixed --log-bin=/data/mysql/mysql_3307/log/mysql3307_bin --sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
mysql2925 1 0 11:56 pts/0 00:00:03 mysqld --datadir=/data/mysql/mysql_3308/data --port=3308 --server_id=3308 --socket=/tmp/mysql_3308.sock --log-output=file --slow_query_log=1 --long_query_time=1 --slow_query_log_file=/data/mysql/mysql_3308/log/slow.log --log-error=/data/mysql/mysql_3308/log/error.log --binlog_format=mixed --log-bin=/data/mysql/mysql_3308/log/mysql3308_bin --sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
root27083 2124 1 12:29 pts/0 00:00:00grep mysql
[root@localhost ~]#
7555
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
