CentOS6.0安装MySQL5.6及配置.docx

CentOS 6.0安装MySQL 5.6及配置从今年3月份开始mysql官网开始发布相关的5.6系列的各个版本，对于mysql5.6系列的版本对一起的版本进行了全局性的细节性加强；个人感觉，以下是在虚拟机中配置的mysql5.6.10源码安装的过程分享记录下：一、必要软件包安装：rootmysql5#yum-yinstallgccgcc-c+gcc-g77autoconfautomakezlib*fiex*libxml*ncurses-devellibmcrypt*libtool-ltdl-devel*makecmake二、编译安装：rootmysql5 # groupadd mysqlrootmysql5 # useradd -r -g mysql mysqlrootmysql5 # lsanaconda-ks.cfg install.log install.log.syslog mysql-5.6.10.tar.gzrootmysql5 # cd /usr/local/rootmysql5 local# lsbin etc games include lib libexec sbin share srcrootmysql5 local# cp /root/mysql-5.6.10.tar.gz /usr/local/rootmysql5 local# lltotal 34468drwxr-xr-x 2 root root 4096 Jan 27 2010 bindrwxr-xr-x 2 root root 4096 Jan 27 2010 etcdrwxr-xr-x 2 root root 4096 Jan 27 2010 gamesdrwxr-xr-x 2 root root 4096 Jan 27 2010 includedrwxr-xr-x 2 root root 4096 Jan 27 2010 libdrwxr-xr-x 2 root root 4096 Jan 27 2010 libexec-rw-r-r- 1 root root 35174149 Apr 17 00:55 mysql-5.6.10.tar.gzdrwxr-xr-x 2 root root 4096 Jan 27 2010 sbindrwxr-xr-x 4 root root 4096 Apr 17 00:32 sharedrwxr-xr-x 2 root root 4096 Jan 27 2010 srcrootmysql5 local# tar -zxvf mysql-5.6.10.tar.gzrootmysql5 local# cd mysql-5.6.10 rootmysql5 local# cmake . #编译报如下错误暂不管 63% Building CXX object sql/CMakeFiles/sql.dir/sql_planner.cc.o/root/mysql-5.6.13/sql/sql_planner.cc: In member function void Optimize_table_order:best_access_path(JOIN_TAB*, table_map, uint, bool, double, POSITION*, POSITION*):/root/mysql-5.6.13/sql/sql_planner.cc:431: warning: loose_scan_opt.Loose_scan_opt:best_loose_scan_start_key may be used uninitialized in this function/root/mysql-5.6.13/sql/sql_planner.cc:431: warning: loose_scan_opt.Loose_scan_opt:best_max_loose_keypart may be used uninitialized in this function/root/mysql-5.6.13/sql/sql_planner.cc:431: warning: loose_scan_opt.Loose_scan_opt:best_loose_scan_records may be used uninitialized in this function/root/mysql-5.6.13/sql/sql_planner.cc:431: warning: loose_scan_opt.Loose_scan_opt:best_loose_scan_key may be used uninitialized in this function/root/mysql-5.6.13/sql/sql_planner.cc:431: warning: loose_scan_opt.Loose_scan_opt:quick_max_loose_keypart may be used uninitialized in this functionrootmysql5 local# make & make install三、配置MySQLrootmysql5 mysql-5.6.10# chown -R mysql.mysql /usr/local/mysqlrootmysql5 mysql-5.6.10#rootmysql5 mysql-5.6.10# cd /usr/local/mysql/scripts/rootmysql5 scripts# ./mysql_install_db -user=mysql -basedir=/usr/local/mysql -datadir=/usr/local/mysql/dataInstalling MySQL system tables.2013-04-17 01:26:58 0 Warning TIMESTAMP with implicit DEFAULT value is deprecated. Please use -explicit_defaults_for_timestamp server option (see documentation for more details).2013-04-17 01:26:58 18715 Note InnoDB: The InnoDB memory heap is disabled2013-04-17 01:26:58 18715 Note InnoDB: Mutexes and rw_locks use InnoDBs own implementation2013-04-17 01:26:58 18715 Note InnoDB: Compressed tables use zlib 1.2.32013-04-17 01:26:58 18715 Note InnoDB: CPU does not support crc32 instructions2013-04-17 01:26:58 18715 Note InnoDB: Initializing buffer pool, size = 128.0M2013-04-17 01:26:58 18715 Note InnoDB: Completed initialization of buffer pool2013-04-17 01:26:58 18715 Note InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!2013-04-17 01:26:59 18715 Note InnoDB: Setting file ./ibdata1 size to 12 MB2013-04-17 01:26:59 18715 Note InnoDB: Database physically writes the file full: wait.2013-04-17 01:26:59 18715 Note InnoDB: Setting log file ./ib_logfile101 size to 48 MB2013-04-17 01:26:59 18715 Note InnoDB: Setting log file ./ib_logfile1 size to 48 MB2013-04-17 01:26:59 18715 Note InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile02013-04-17 01:26:59 18715 Warning InnoDB: New log files created, LSN=457812013-04-17 01:26:59 18715 Note InnoDB: Doublewrite buffer not found: creating new2013-04-17 01:26:59 18715 Note InnoDB: Doublewrite buffer created2013-04-17 01:26:59 18715 Note InnoDB: 128 rollback segment(s) are active.2013-04-17 01:26:59 18715 Warning InnoDB: Creating foreign key constraint system tables.2013-04-17 01:26:59 18715 Note InnoDB: Foreign key constraint system tables created2013-04-17 01:26:59 18715 Note InnoDB: Creating tablespace and datafile system tables.2013-04-17 01:26:59 18715 Note InnoDB: Tablespace and datafile system tables created.2013-04-17 01:26:59 18715 Note InnoDB: Waiting for purge to start2013-04-17 01:26:59 18715 Note InnoDB: 1.2.10 started; log sequence number 02013-04-17 01:26:59 18715 Note Binlog end2013-04-17 01:26:59 18715 Note InnoDB: FTS optimize thread exiting.2013-04-17 01:26:59 18715 Note InnoDB: Starting shutdown.2013-04-17 01:27:00 18715 Note InnoDB: Shutdown completed; log sequence number 1625977OKFilling help tables.2013-04-17 01:27:00 0 Warning TIMESTAMP with implicit DEFAULT value is deprecated. Please use -explicit_defaults_for_timestamp server option (see documentation for more details).2013-04-17 01:27:00 18738 Note InnoDB: The InnoDB memory heap is disabled2013-04-17 01:27:00 18738 Note InnoDB: Mutexes and rw_locks use InnoDBs own implementation2013-04-17 01:27:00 18738 Note InnoDB: Compressed tables use zlib 1.2.32013-04-17 01:27:00 18738 Note InnoDB: CPU does not support crc32 instructions2013-04-17 01:27:00 18738 Note InnoDB: Initializing buffer pool, size = 128.0M2013-04-17 01:27:00 18738 Note InnoDB: Completed initialization of buffer pool2013-04-17 01:27:00 18738 Note InnoDB: Highest supported file format is Barracuda.2013-04-17 01:27:00 18738 Note InnoDB: 128 rollback segment(s) are active.2013-04-17 01:27:00 18738 Note InnoDB: Waiting for purge to start2013-04-17 01:27:01 18738 Note InnoDB: 1.2.10 started; log sequence number 16259772013-04-17 01:27:01 18738 Note Binlog end2013-04-17 01:27:01 18738 Note InnoDB: FTS optimize thread exiting.2013-04-17 01:27:01 18738 Note InnoDB: Starting shutdown.2013-04-17 01:27:02 18738 Note InnoDB: Shutdown completed; log sequence number 1625987OKTo start mysqld at boot time you have to copysupport-files/mysql.server to the right place for your systemPLEASE 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 mysql5.6 password new-passwordAlternatively you can run: /usr/local/mysql/bin/mysql_secure_installationwhich will also give you the option of removing the testdatabases and anonymous user created by default. This isstrongly recommended for production servers.See the manual for more 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.plPlease report any problems with the ./bin/mysqlbug script!The latest information about MySQL is available on the web at Support MySQL by buying support/licenses at New default config file was created as /usr/local/mysql/f andwill be used by default by the server when you start it.You may edit this file to change server settingsWARNING: Default config file /etc/f exists on the systemThis file will be read by default by the MySQL serverIf you do not want to use this, either remove it, or use the-defaults-file argument to mysqld_safe when starting the serverrootmysql5 scripts# rootmysql5 scripts# cd /usr/local/mysql/support-files/rootmysql5 support-files# cp mysql.server /etc/rc.d/init.d/mysqlcp: overwrite /etc/rc.d/init.d/mysql? yrootmysql5 support-files# cp f /etc/f cp: overwrite /etc/f? yrootmysql5 support-files# rootmysql5 support-files# chkconfig -add mysql-add: unknown optionrootmysql5 support-files# chkconfig -add mysqlrootmysql5 support-files# chkconfig mysql onrootmysql5 support-files# service mysql startStarting MySQL. OK rootmysql5 support-files# rootmysql5 data# mysql -u mysql -p -S /tmp/mysql.sock Enter password: Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 1Server version: 5.6.10 Source distributionType help; or h for help. Type c to clear the buffer.mysqlmysql status;-mysql Ver 14.12 Distrib 5.0.77, for RedHat-linux-gnu (i686) using readline 5.1Connection id: 1Current database:Current user: mysqllocalhostSSL: Not in useCurrent pager: stdoutUsing outfile: Using delimiter: ;Server version: 5.6.10 Source distributionProtocol version: 10Connection: Localhost via UNIX socketServer characterset: latin1Db characterset: latin1Client characterset: latin1Conn. characterset: latin1UNIX socket: /tmp/mysql.sockUptime: 3 min 38 secThreads: 1 Questions: 5 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 60 Queries per second avg: 0.022-mysql四、 远程登录及常用命令1、mysql服务的启动和停止 net stop mysql net start mysql2、本地登陆mysql 及远程登录 语法如下： mysql -u用户名 -p用户密码 键入命令mysql u root -p， 回车后提示你输入密码，输入12345，然后回车即可进入到mysql中了，mysql的提示符是：mysql注意，如果是连接到另外的机器上，则需要加入一个参数-h机器IP但被远程登录的mysql服务器需增加登录账号，方法如下：mysqlGRANT ALL PRIVILEGES ON *.* TO USER1% IDENTIFIED BY passwordmysqlFLUSH PRIVILEGES;3、增加新用户 格式：grant 权限 on 数据库.* to 用户名登录主机 identified by 密码 如，增加一个用户user1密码为password1，让其可以在本机上登录， 并对所有数 据库有查询、插入、修改、删除的权限。首先用以root用户连入mysql，然后键入以下命令： grant select,insert,update,delete on *.* to user1localhost Identified by password1;如果希望该用户能够在任何机器上登陆mysql，则将localhost改为%。如果你不想user1有密码，可以再打一个命令将密码去掉。grant select,insert,update,delete on mydb.* to user1localhost identified by ;4： 操作数据库 登录到mysql中，然后在mysql的提示符下运行下列命令，每个命令以分号结束。1、 显示数据库列表。show databases; 缺省有两个数据库：mysql和test。 mysql库存放着mysql的系统和用户权限信息，我们改密码和新增用户，实际上就是对这个库进行操作。2、 显示库中的数据表： use mysql; show tables;3、 显示数据表的结构： describe 表名;4、 建库与删库： create database 库名; drop database 库名;5、 建表： use 库名; create table 表名(字段列表); drop table 表名;6、 清空表中记录： delete from 表名;7、 显示表中的记录： select * from 表名;第五招、导出和导入数据1. 导出数据： mysqldump -opt test mysql.test即将数据库test数据库导出到mysql.test文件，后者是一个文本文件如：mysqldump -u root -p123456 -databases dbname mysql.dbname就是把数据库dbname导出到文件mysql.dbname中。2. 导入数据: mysqlimport -u root -p123456 SHOW DATABASES;2:2、创建一个数据库MYSQLDATA mysql CREATE DATABASE MYSQLDATA