1) Requires
root access on target server
2) Download
the latest stable MySQL release from http://www.mysql.com/downloads/ and unzip
it. It requires login account on Oracle Software Delivery Cloud. Select a
Product Pack as MySQL Database and Platform as Linux x86-64.
cd /tmp
# unzip V43500-01.zip
Archive: V43500-01.zip
extracting: MySQL-client-advanced-5.6.16-1.el6.x86_64.rpm
extracting: MySQL-test-advanced-5.6.16-1.el6.x86_64.rpm
extracting: MySQL-shared-compat-advanced-5.6.16-1.el6.x86_64.rpm
extracting: MySQL-server-advanced-5.6.16-1.el6.x86_64.rpm
extracting: MySQL-embedded-advanced-5.6.16-1.el6.x86_64.rpm
extracting: MySQL-devel-advanced-5.6.16-1.el6.x86_64.rpm
extracting: MySQL-shared-advanced-5.6.16-1.el6.x86_64.rpm
extracting: README.txt
3) Remove
the existing default MySQL.
# rpm -qa | grep
-i mysql
mysql-libs-5.1.71-1.el6.x86_64
4) MYSQL-shared-compat
replaces the Red Hat mysql-libs package, thus satisfying dependencies of other
packages on mysql-libs.
# rpm -ivh
MySQL-shared-compat-advanced-5.6.16-1.el6.x86_64.rpm
Preparing...
########################################### [100%]
1:MySQL-shared-compat-adv###########################################
[100%]
# rpm -ivh
MySQL-shared-advanced-5.6.16-1.el6.x86_64.rpm
Preparing...
########################################### [100%]
1:MySQL-shared-advanced ###########################################
[100%]
# rpm -e
mysql-libs
warning:
/etc/my.cnf saved as /etc/my.cnf.rpmsave
# rpm -ivh
MySQL-server-advanced-5.6.16-1.el6.x86_64.rpm MySQL-client-advanced-5.6.16-1.el6.x86_64.rpm
Preparing... ###########################################
[100%]
1:MySQL-client-advanced ########################################### [
50%]
2:MySQL-server-advanced ###########################################
[100%]
2013-12-19
21:02:09 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated.
Please use --explicit_defaults_for_timestamp server option (see documentation
for more details).
2013-12-19
21:02:10 2906 [Note] InnoDB: The InnoDB memory heap is disabled
2013-12-19
21:02:10 2906 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2013-12-19
21:02:10 2906 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-12-19
21:02:10 2906 [Note] InnoDB: Using Linux native AIO
2013-12-19
21:02:10 2906 [Note] InnoDB: Not using CPU crc32 instructions
2013-12-19
21:02:10 2906 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-12-19
21:02:10 2906 [Note] InnoDB: Completed initialization of buffer pool
2013-12-19
21:02:10 2906 [Note] InnoDB: The first specified data file ./ibdata1 did not
exist: a new database to be created!
2013-12-19
21:02:10 2906 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2013-12-19
21:02:10 2906 [Note] InnoDB: Database physically writes the file full: wait...
2013-12-19
21:02:10 2906 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2013-12-19
21:02:11 2906 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2013-12-19
21:02:13 2906 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2013-12-19
21:02:13 2906 [Warning] InnoDB: New log files created, LSN=45781
2013-12-19
21:02:13 2906 [Note] InnoDB: Doublewrite buffer not found: creating new
2013-12-19
21:02:13 2906 [Note] InnoDB: Doublewrite buffer created
2013-12-19
21:02:13 2906 [Note] InnoDB: 128 rollback segment(s) are active.
2013-12-19
21:02:13 2906 [Warning] InnoDB: Creating foreign key constraint system tables.
2013-12-19
21:02:17 2906 [Note] InnoDB: Foreign key constraint system tables created
2013-12-19
21:02:17 2906 [Note] InnoDB: Creating tablespace and datafile system tables.
2013-12-19
21:02:17 2906 [Note] InnoDB: Tablespace and datafile system tables created.
2013-12-19
21:02:17 2906 [Note] InnoDB: Waiting for purge to start
2013-12-19
21:02:17 2906 [Note] InnoDB: 5.6.15 started; log sequence number 0
2013-12-19
21:02:21 2906 [Note] RSA private key file not found:
/var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2013-12-19
21:02:21 2906 [Note] RSA public key file not found:
/var/lib/mysql//public_key.pem. Some authentication plugins will not work.
A random root
password has been set. You will find it in '/root/.mysql_secret'.
2013-12-19
21:02:24 2906 [Note] Binlog end
2013-12-19
21:02:24 2906 [Note] InnoDB: FTS optimize thread exiting.
2013-12-19
21:02:24 2906 [Note] InnoDB: Starting shutdown...
2013-12-19
21:02:25 2906 [Note] InnoDB: Shutdown completed; log sequence number 1625977
2013-12-19
21:02:26 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated.
Please use --explicit_defaults_for_timestamp server option (see documentation
for more details).
2013-12-19
21:02:26 2930 [Note] InnoDB: The InnoDB memory heap is disabled
2013-12-19
21:02:26 2930 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2013-12-19
21:02:26 2930 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-12-19
21:02:26 2930 [Note] InnoDB: Using Linux native AIO
2013-12-19
21:02:26 2930 [Note] InnoDB: Not using CPU crc32 instructions
2013-12-19
21:02:26 2930 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-12-19
21:02:26 2930 [Note] InnoDB: Completed initialization of buffer pool
2013-12-19
21:02:26 2930 [Note] InnoDB: Highest supported file format is Barracuda.
2013-12-19
21:02:26 2930 [Note] InnoDB: 128 rollback segment(s) are active.
2013-12-19
21:02:26 2930 [Note] InnoDB: Waiting for purge to start
2013-12-19
21:02:26 2930 [Note] InnoDB: 5.6.15 started; log sequence number 1625977
2013-12-19
21:02:26 2930 [Note] RSA private key file not found:
/var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2013-12-19
21:02:26 2930 [Note] RSA public key file not found:
/var/lib/mysql//public_key.pem. Some authentication plugins will not work.
2013-12-19
21:02:26 2930 [Note] Binlog end
2013-12-19
21:02:26 2930 [Note] InnoDB: FTS optimize thread exiting.
2013-12-19
21:02:26 2930 [Note] InnoDB: Starting shutdown...
2013-12-19
21:02:28 2930 [Note] InnoDB: Shutdown completed; log sequence number 1625987
A RANDOM
PASSWORD HAS BEEN SET FOR THE MySQL root USER !
You will find
that password in '/root/.mysql_secret'.
You must change
that password on your first connect,
no other
statement but 'SET PASSWORD' will be accepted.
See the manual
for the semantics of the 'password expired' flag.
Also, the
account for the anonymous user has been removed.
In addition, you
can run:
/usr/bin/mysql_secure_installation
which will also
give you the option of removing the test database.
This is strongly
recommended for production servers.
See the manual
for more instructions.
Please report
any problems with the /usr/bin/mysqlbug script!
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
New default
config file was created as /usr/my.cnf and
will be used by
default by the server when you start it.
You may edit
this file to change server settings
# service mysql
start
Starting
MySQL...... [ OK ]
5) Perform
post install security activities. The random password generated for root is in
/root/.mysql_secret.
#
/usr/bin/mysql_secure_installation
NOTE:
RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In
order to log into MySQL to secure it, we'll need the current
password
for the root user. If you've just
installed MySQL, and
you
haven't set the root password yet, the password will be blank,
so
you should just press enter here.
Enter
current password for root (enter for none):
OK,
successfully used password, moving on...
Setting
the root password ensures that nobody can log into the MySQL
root
user without the proper authorisation.
You
already have a root password set, so you can safely answer 'n'.
Change
the root password? [Y/n] Y
New
password:
Re-enter
new password:
Password
updated successfully!
Reloading
privilege tables..
... Success!
By
default, a MySQL installation has an anonymous user, allowing anyone
to
log into MySQL without having to have a user account created for
them. This is intended only for testing, and to
make the installation
go
a bit smoother. You should remove them
before moving into a
production
environment.
Remove
anonymous users? [Y/n] Y
... Success!
Normally,
root should only be allowed to connect from 'localhost'. This
ensures
that someone cannot guess at the root password from the network.
Disallow
root login remotely? [Y/n] Y
... Success!
By
default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and
should be removed
before
moving into a production environment.
Remove
test database and access to it? [Y/n] Y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading
the privilege tables will ensure that all changes made so far
will
take effect immediately.
Reload
privilege tables now? [Y/n] Y
... Success!
All
done! If you've completed all of the
above steps, your MySQL
installation
should now be secure.
Thanks
for using MySQL!
Cleaning
up...
MySQL
Installation Layout for Linux RPM packages.
Directory/File
|
Contents
|
/usr/bin
|
Client
programs and scripts
|
/usr/sbin
|
The
mysqld server
|
/var/lib/mysql
|
Databases,
Log files
|
/usr/share/info
|
Manual
in info format
|
/usr/share/man
|
Unix
manual pages
|
/usr/include/mysql
|
Header
files
|
/usr/lib/mysql
|
Libraries
|
/usr/share/mysql
|
Miscellaneous
support files, error messages, character set files, sample configuration
files, database install scripts
|
/usr/share/sql-bench
|
Benchmarks
|
/root/.mysql_secret
|
Default
root password file
|
/usr/my.cnf
|
Configuration
file
|
/usr/bin/mysql_secure_installation
|
Post
install security script
|
/usr/bin/mysqlbug
|
Script
to report any problems
|
#
mysql -u root -p
Enter
password:
Create
user and grant privileges
mysql>
CREATE USER 'abitra'@'localhost' IDENTIFIED BY 'passwd';
mysql>
GRANT ALL PRIVILEGES ON *.* TO 'abitra'@'localhost' WITH GRANT OPTION;
mysql>
CREATE USER 'abitra'@'%' IDENTIFIED BY 'passwd';
mysql>
GRANT ALL PRIVILEGES ON *.* TO 'abitra'@'%' WITH GRANT OPTION;
mysql>
FLUSH PRIVILEGES;
The
asterisks refer to the database and table respectively.
To
display which accounts exist and check whether their passwords are empty.
mysql>
SELECT User, Host, Password FROM mysql.user;
+--------+-----------+-------------------------------------------+
|
User | Host | Password |
+--------+-----------+-------------------------------------------+
|
root | localhost |
*BEE13BB659FAA4D3ABFA2A849B1F9A6776DB65E2 |
|
root | 127.0.0.1 |
*BEE13BB659FAA4D3ABFA2A849B1F9A6776DB65E2 |
|
root | ::1 |
*BEE13BB659FAA4D3ABFA2A849B1F9A6776DB65E2 |
|
abitra | % |
*4F281816E24012FEE52F4F5D469318355F9D482D |
+--------+-----------+-------------------------------------------+
4
rows in set (0.01 sec)
mysql>
SHOW DATABASES;
+--------------------+
|
Database |
+--------------------+
|
information_schema |
|
mysql |
|
performance_schema |
+--------------------+
3
rows in set (0.05 sec)
mysql>
CREATE DATABASE dbname;
mysql>
DROP DATBASE dbname;
mysql
> USE information_schema;
mysql>
SHOW TABLES;
Startup and
Shutdown MySQL Server
Execute
any one of these commands to startup or shutdown MySQL database server.
Shutdown
commands:
$
mysqladmin -u root -p shutdown
#
service mysql stop
#
/etc/rc.d/init.d/mysql stop
Startup
commands:
$
/usr/bin/mysqld_safe &
#
service mysql start
#
/etc/rc.d/init.d/mysql start
Reset root
password
If
you don’t know the current password, start MySQL server to connect it without
password.
#
mysqld_safe --skip-grant-tables &
#
mysql -u root
mysql>
UPDATE mysql.user SET password=PASSWORD(‘newpwd’) WHERE user=’root’;
mysql>
FLUSH PRIVILEGES;
mysql>
exit
If
you know the password, connect to the server.
#
mysql -u root -p
mysql>
UPDATE mysql.user SET password=PASSWORD(‘newpwd’) WHERE user=’root’;
mysql>
FLUSH PRIVILEGES;
mysql>
exit
Configuration
settings
The
location for config file is /etc/mysql/my.cnf
[mysql]
# CLIENT #
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
# GENERAL #
user = mysql
default-storage-engine = InnoDB
socket =
/var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysqld.pid
# MyISAM #
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP
# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
#sql-mode =
STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
#sysdate-is-now = 1
#innodb = FORCE
#innodb-strict-mode = 1
# DATA STORAGE #
datadir = /var/lib/mysql
# BINARY LOGGING #
log-bin = /opt/mysql/arch/mysql-bin
binlog_format
= mixed
expire-logs-days = 14
sync-binlog = 1
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 750
thread-cache-size = 64
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 1024
#max_allowed_packet
= 16M
#thread_stack
= 256K
#query_cache_limit
= 8M
#query_cache_size
= 128M
#query_cache_type
= 1
#table_cache
= 1024
#wait_timeout
= 300
# INNODB #
innodb-flush-method =
O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size
= 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table
= 1
innodb-buffer-pool-size = 4G
innodb_log_buffer_size = 64M
#innodb_additional_mem_pool_size=20M
#innodb_lock_wait_timeout=50
#innodb_thread_concurrency
= 8
# LOGGING #
#log-error
= /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log
= 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
[mysqlbackup]
# BACKUP #
backup_dir = /opt/mysql/backup
socket =
/var/lib/mysql/mysql.sock
To check what
engine the tables are using
mysql> SHOW TABLE STATUS;
To check
MySQL problem
mysql> SHOW ENGINE INNODB STATUS;
# innochecksum /var/lib/mysql/ibdata1
Reconfiguring InnoDB settings
1.
Stop MySQL.
#
service mysql stop
2.
Edit the InnoDB setting as above.
3.
Move the below old InnoDB log files to backup
location.
/var/lib/mysql/ib_logfile0
/var/lib/mysql/ib_logfile1
4.
Start MySQL.
#
service mysql start
