MySQL Installation and Configuration | Beginner's Tutorial

\\n\\n

MySQL is the most popular relational database management system, developed by Swedish company MySQL AB, and currently owned by Oracle Corporation.

\\n\\n

The SQL language used by MySQL is the most commonly used standardized language for accessing databases.

\\n\\n

Due to its small size, fast speed, low total cost of ownership, and especially its open source nature, MySQL is generally chosen as the website database for the development of small and medium-sized websites.

\\n\\n

---

\\n\\n

MySQL Installation

\\n\\n

System platform for this tutorial: CentOS release 6.6 (Final) 64-bit.

\\n\\n

1. Install Compilation Tools and Library Files

\\n\\n

yum -y install gcc gcc-c++ make autoconf libtool-ltdl-devel gd-devel freetype-devel libxml2-devel libjpeg-devel libpng-devel openssl-devel curl-devel bison patch unzip libmcrypt-devel libmhash-devel ncurses-devel sudo bzip2 flex libaio-devel

\\n\\n

2. Install cmake Compiler

\\n\\n

cmake version: cmake-3.1.1.

\\n\\n

1. Download address: http://www.cmake.org/files/v3.1/cmake-3.1.1.tar.gz

\\n\\n

$ wget http://www.cmake.org/files/v3.1/cmake-3.1.1.tar.gz

\\n

\\n\\n

2. Extract the installation package

\\n\\n

$ tar zxvf cmake-3.1.1.tar.gz

\\n\\n

3. Enter the installation package directory

\\n\\n

$ cd cmake-3.1.1

\\n\\n

4. Compile and install

\\n\\n

$ ./bootstrap\\n$ make && make install

\\n\\n

---

\\n\\n

3. Install MySQL

\\n\\n

MySQL version: mysql-5.6.15.

\\n\\n

1. Download address: http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.15.tar.gz

\\n\\n

$ wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.15.tar.gz

\\n

\\n\\n

2. Extract the installation package

\\n\\n

$ tar zxvf mysql-5.6.15.tar.gz

\\n\\n

3. Enter the installation package directory

\\n\\n

$ cd mysql-5.6.15

\\n\\n

4. Compile and install

\\n\\n

$ cmake -DCMAKE_INSTALL_PREFIX=/usr/local/webserver/mysql/ -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DWITH_INNODB_MEMCACHED=1 -DWITH_DEBUG=OFF -DWITH_ZLIB=bundled -DENABLED_LOCAL_INFILE=1 -DENABLED_PROFILING=ON -DMYSQL_MAINTAINER_MODE=OFF -DMYSQL_DATADIR=/usr/local/webserver/mysql/data -DMYSQL_TCP_PORT=3306\\n$ make && make install

\\n\\n

5. Check mysql version:

\\n\\n

$ /usr/local/webserver/mysql/bin/mysql --version

\\n

\\n\\n

At this point, mysql installation is complete.

\\n\\n

---

\\n\\n

MySQL Configuration

\\n\\n

1. Create the mysql user for running mysql:

\\n\\n

$ /usr/sbin/groupadd mysql\\n$ /usr/sbin/useradd -g mysql mysql

\\n\\n

2. Create binlog and database storage paths and grant mysql user permissions

\\n\\n

$ mkdir -p /usr/local/webserver/mysql/binlog /www/data_mysql\\n$ chown mysql.mysql /usr/local/webserver/mysql/binlog/ /www/data_mysql/

\\n\\n

3. Create my.cnf configuration file

\\n\\n

Replace /etc/my.cnf with the following content

\\n\\n

$ cat /etc/my.cnf\\n\\n\\nport = 3306\\nsocket = /tmp/mysql.sock\\n\\n\\nreplicate-ignore-db = mysql\\nreplicate-ignore-db = test\\nreplicate-ignore-db = information_schema\\nuser = mysql\\nport = 3306\\nsocket = /tmp/mysql.sock\\nbasedir = /usr/local/webserver/mysql\\ndatadir = /www/data_mysql\\nlog-error = /usr/local/webserver/mysql/mysql_error.log\\npid-file = /usr/local/webserver/mysql/mysql.pid\\nopen_files_limit = 65535\\nback_log = 600\\nmax_connections = 5000\\nmax_connect_errors = 1000\\ntable_open_cache = 1024\\nexternal-locking = FALSE\\nmax_allowed_packet = 32M\\nsort_buffer_size = 1M\\njoin_buffer_size = 1M\\nthread_cache_size = 600\\n#thread_concurrency = 8\\nquery_cache_size = 128M\\nquery_cache_limit = 2M\\nquery_cache_min_res_unit = 2k\\ndefault-storage-engine = MyISAM\\ndefault-tmp-storage-engine=MYISAM\\nthread_stack = 192K\\ntransaction_isolation = READ-COMMITTED\\ntmp_table_size = 128M\\nmax_heap_table_size = 128M\\nlog-slave-updates\\nlog-bin = /usr/local/webserver/mysql/binlog/binlog\\nbinlog-do-db=oa_fb\\nbinlog-ignore-db=mysql\\nbinlog_cache_size = 4M\\nbinlog_format = MIXED\\nmax_binlog_cache_size = 8M\\nmax_binlog_size = 1G\\nrelay-log-index = /usr/local/webserver/mysql/relaylog/relaylog\\nrelay-log-info-file = /usr/local/webserver/mysql/relaylog/relaylog\\nrelay-log = /usr/local/webserver/mysql/relaylog/relaylog\\nexpire_logs_days = 10\\nkey_buffer_size = 256M\\nread_buffer_size = 1M\\nread_rnd_buffer_size = 16M\\nbulk_insert_buffer_size = 64M\\nmyisam_sort_buffer_size = 128M\\nmyisam_max_sort_file_size = 10G\\nmyisam_repair_threads = 1\\nmyisam_recover\\ninteractive_timeout = 120\\nwait_timeout = 120\\nskip-name-resolve\\n#master-connect-retry = 10\\nslave-skip-errors = 1032,1062,126,1114,1146,1048,1396\\n#master-host = 192.168.1.2\\n#master-user = username\\n#master-password = password\\n#master-port = 3306\\nserver-id = 1\\n\\nloose-innodb-trx=0\\nloose-innodb-locks=0\\nloose-innodb-lock-waits=0\\nloose-innodb-cmp=0\\nloose-innodb-cmp-per-index=0\\nloose-innodb-cmp-per-index-reset=0\\nloose-innodb-cmp-reset=0\\nloose-innodb-cmpmem=0\\nloose-innodb-cmpmem-reset=0\\nloose-innodb-buffer-page=0\\nloose-innodb-buffer-page-lru=0\\nloose-innodb-buffer-pool-stats=0\\nloose-innodb-metrics=0\\nloose-innodb-ft-default-stopword=0\\nloose-innodb-ft-inserted=0\\nloose-innodb-ft-deleted=0\\nloose-innodb-ft-being-deleted=0\\nloose-innodb-ft-config=0\\nloose-innodb-ft-index-cache=0\\nloose-innodb-ft-index-table=0\\nloose-innodb-sys-tables=0\\nloose-innodb-sys-tablestats=0\\nloose-innodb-sys-indexes=0\\nloose-innodb-sys-columns=0\\nloose-innodb-sys-fields=0\\nloose-innodb-sys-foreign=0\\nloose-innodb-sys-foreign-cols=0\\n\\nslow_query_log_file=/usr/local/webserver/mysql/mysql_slow.log\\nlong_query_time = 1\\n\\n\\nquick\\nmax_allowed_packet = 32M

\\n\\n

4. Initialize the database

\\n\\n

$/usr/local/webserver/mysql/scripts/mysql_install_db --defaults-file=/etc/my.cnf --user=mysql

\\n\\n

The following information is displayed:

\\n\\n

Installing MySQL system tables...2015-01-26 20:18:51 0  TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).\\nOK\\n\\nFilling help tables...2015-01-26 20:18:57 0  TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).\\nOK\\n...

\\n\\n

5. Create startup script

\\n\\n

$ cd /usr/local/webserver/mysql/\\n$ cp support-files/mysql.server /etc/rc.d/init.d/mysqld\\n$ chkconfig --add mysqld\\n$ chkconfig --level 35 mysqld on

\\n\\n

6. Start mysql server

\\n\\n

$ service mysqld start

\\n

\\n\\n

7. Connect to MySQL

\\n\\n

$ /usr/local/webserver/mysql/bin/mysql -u root -p

\\n

\\n\\n

Change MySQL User Password

\\n\\n

mysqladmin -uUsername -pOldPassword password NewPassword

\\n\\n

Or enter the mysql command line

\\n\\n

SET PASSWORD FOR 'Username'@'Host' = PASSWORD('Password');

\\n\\n

Create new user and grant privileges:

\\n\\n

grant all privileges on *.* to Username@'%' identified by 'Password' with grant option;

\\n\\n

Other Commands

\\n\\n

\\n
• Start: service mysqld start
\\n
• Stop: service mysqld stop
\\n
• Restart: service mysqld restart
\\n
• Reload configuration: service mysqld reload
\\n