MySQL Node deployment instructions

MySQL Node deployment instructions

1.MySQL Compilation and Installation

Considering the features of MySQL multi-version and multi-branch, comprehensive performance, availability, cost and other factors, we recommend using Percona company developed MySQL, which mainly uses the XTRADB storage engine (for the characteristics of this storage engine, later specifically introduced), fully compatible with InnoDB, Enhanced on a innodb_plugin basis, supports the acid (atomicity, consistency, isolation, persistence) of standard transactions, supports MVCC (Multi-version Concurrency control, provides concurrency control), better performance, Open source is free to use, while Percona also provides an open source physical Backup tool (Xtrabackup) that enables fast physical backups of databases using the XTRADB storage engine. More information about this branch can be viewed in the blog: http://www.mysqlperformanceblog.com/.

Considering the maturity and stability of the version, we recommend downloading the MySQL-5.1 version of the source code, download the address list see: HTTP://WWW.PERCONA.COM/DOWNLOADS/PERCONA-SERVER-5.1/, You can consider the choice of 5.1.47-11.2 (source code size 23.7 MB), this version of NetEase line is also on a large-scale use, from the use of the effect of the view, or very stable.

Before installing, you need to check if some of the libraries required to compile MySQL are already installed. In a typical Linux system, you may need to install these dependent libraries with the following commands:

autoconf automake1.9 libtool M4 libncurses5-dev Bison ncurses-devel gcc g++

Download the MySQL source code from the Percona website and save it locally

Http://www.percona.com/downloads/Percona-Server-5.1/Percona-Server-5.1.47-11.2/source/Percona-Server.tar.gz, Or you can pick it up from me and patch the MySQL part.

Unzip the compressed file into the $dir directory

Go to $dir, add configure file as executable permission

$ CD $dir

$ chmod +x Configure

Start compiling the installation and perform the following three steps

$./configure--prefix=/usr/local/mysql--with-extra-charsets=all--enable-local-infile-- Enable-thread-safe-client--enable-assembler--with-client-ldflags=-all-static--with-mysqld-ldflags=-all-static- -with-plugins=innodb_plugin,partition--with-big-tables--with-readline

$ make

$ make install//because it is installed in the/usr directory, this step needs to be switched to the root user to execute

MySQL source code Compilation parameter description

Parameter implication

--prefix Specifies the MySQL installation path, preferably installed into the specified directory

--with-extra-charsets Specifying the installation of the extended character set

--enable-local-infile Specifies that local import files are allowed

--enable-thread-safe-client Specifying thread-Safe mode

--enable-assembler specifying support assembly for improved performance

--with-client-ldflags Static compilation, improve performance 13%

--with-mysqld-ldflags Static compilation, improve performance 13%

--with-plugins=innodb_plugin,partition Specifies the installation of the XTRADB storage engine, partition plug-in, if you want to take advantage of the partitioning feature, you must specify the partition plugin

--with-big-tables specifying support for oversized tables

After compiling, you need to create the required configuration files for MySQL startup in the appropriate directory.

Common exceptions during the compilation process:

Checking for termcap functions Library ... configure:error:No curses/termcap Library found

Ncurses-devel not installed

.. /depcomp:line 571:exec:g++: Not Found

g++ not installed

Warning:bison executable not found in PATH

Bision not installed

2.MySQL Configuration Start-up

After the MySQL installation, you need to do the appropriate configuration, and then start MySQL.

Disable Swap

Su Root

echo 0 >/proc/sys/vm/swappiness

If the machine restarts, the value will be restored to 100, in order to achieve a permanent

For a long time, you can modify the/etc/sysctl.conf file to add vm.swappiness=0 on the/etc/sysctl.conf. This

Need to explain the reasons.

Linux has a lot of good memory, IO scheduling mechanism, but it does not apply to all scenarios. For DBAs,

One of the headaches of Linux is that it does not prevent MySQL from being assigned to MySQL because it is important

The address space is mapped to swap. For systems with frequent read and write operations, the data appears to be in memory and actually

The disk is very bad and the response time growth is likely to directly drag down the entire system. As a DBA, try to avoid

MySQL uses swap? For example, why swap is generated. Suppose our physical memory is 16g,swap is 4G.

If MySQL itself already occupies 12G of physical memory, while other programs or system modules need 6G of memory,

At this point the operating system may map a portion of the address space that MySQL has to the swap.

The parameter vm.swappiness on/etc/sysctl.conf determines whether Linux is inclined to use swap or is prone to

To release the file system cache and buffer (the size is how much, you can use the free command under the root user to view

IT).

In the case of tight memory, the lower the value the more inclined to release the file system cache. Of course, this parameter only reduces

The probability of using swap does not prevent Linux from using swap. Think of the memory can be used, why do you want to use swap

It? Swappiness=0 indicates maximum use of physical memory, then swap space, swappiness=

100 indicates active use of the swap partition, and the memory of the data in a timely manner into the swap space.

Create a MySQL Account

Useradd MySQL

passwd MySQL

Create MySQL Data directory, where MySQL data directory and configuration files are best placed in a unified file

directory, the data directory is best used in the form of soft links, here to pay attention to the original data directory permissions, such as:

[Email protected] mysql]$ ls-lh/home/mysql/mysql/

Total 4.0K

-rw-r--r--1 mysql mysql 2.1K Jul 5 14:54 my1.cnf

lrwxrwxrwx 1 mysql MySQL 4 10:50 mysql--/opt/mysql-test/

[Email protected] mysql]$ Ls-lh/opt/|grep mysql-test

Drwxr-xr-x 4 mysql mysql 4.0K Jul 5 14:54 mysql-test

Generate MySQL configuration file

Generate the MySQL profile my.cnf according to the MySQL profile template, and place it under/home/mysql/mysql, my.cnf the following modifications according to the template:

Replace #port_holder# for the port you intend to use here, assuming 4321

Replace the directory where the #path_holder# is stored for data, assuming/home/mysql/mysql/mysql

Replace the #ip_holder# as the current host's intranet IP

Replace #user_holder# for MySQL-enabled users, typically MySQL

The template configuration file is as follows:

[Client]

Port = #port_holder #

Socket = #path_holder #/mysqld.sock

[Mysqld_safe]

user = #user_holder #

Nice = 0

Ledir =/usr/local/mysql/libexec

[Mysqld]

Server-id= 3

bind-address = #ip_holder #//NOTE: If the VIP is configured, this parameter does not set

Port = #port_holder #

Pid-file = #path_holder #/mysqld.pid

Socket = #path_holder #/mysqld.sock

Basedir =/usr/local/mysql

DataDir = #path_holder #

Innodb_data_home_dir = #path_holder #

Innodb_log_group_home_dir = #path_holder #

Tmpdir = #path_holder #

Log-error = #path_holder #/mysqld.log

Slow_query_log =1

Slow_query_log_file = #path_holder #/mysql-slow.log

Log_bin = #path_holder #/mysql-bin.log

Log_slave_updates//Relay operations use also logs to Binary-log

Skip-slave-start//mysql start, do not start replication

Auto_increment_increment = 2//If it is a M-M structure, this parameter is the number of M

Auto_increment_offset = 2//If it is a M-M structure, this parameter is the number of the instance

user = #user_holder #

Language =/usr/local/mysql/share/mysql/english

Table_cache = 512

Long_query_time = 4

Max_connections = 800

Query_cache_type = 0

Default-character-set = UTF8

Default-storage-engine = InnoDB

Skip-external-locking

Expire_logs_days = 7

Max_binlog_size = 100M

Innodb_buffer_pool_size = 200M//According to the actual conditions of the machine, generally 75% of the memory

Innodb_data_file_path = Ibdata1:512m:autoextend

Innodb_autoextend_increment = 128

Innodb_log_files_in_group = 2

Innodb_log_file_size = 512M

Innodb_lock_wait_timeout = 5

Innodb_flush_method = O_direct

Innodb_flush_log_at_trx_commit = 2

innodb_file_per_table= 1

Innodb_file_format =barracuda

Innodb_file_format_check =barracuda

Innodb_adaptive_checkpoint =estimate

Innodb_expand_import = 1

Innodb_read_io_threads = 1

Innodb_stats_method = nulls_unequal

Innodb_thread_concurrency = 12

Innodb_write_io_threads = 1

Innodb_io_capacity =800

[Mysqldump]

Quick

Quote-names

Max_allowed_packet = 16M

Default-character-set = UTF8

[MySQL]

Default-character-set = UTF8

The meaning of each parameter is described in the subsequent optimization document.

Initializing MySQL Database

Cd/home/mysql/mysql

mysql_install_db--defaults-file=my.cnf

Start MySQL

Mysqld_safe--DEFAULTS-FILE=/HOME/MYSQL/MYSQL/MY.CNF &

View a list of files

[Email protected] mysql]$ ls-lh/home/mysql/mysql/mysql/

Total 1.6G

-RW-RW----1 mysql mysql 512M Jul 5 14:54 ibdata1

-RW-RW----1 mysql mysql 512M Jul 5 14:54 Ib_logfile0

-RW-RW----1 mysql mysql 512M Jul 4 10:51 ib_logfile1

-RW-RW----1 MySQL mysql 5 14:54 master.info

drwx------2 mysql mysql 4.0K Jul 4 10:48 MySQL

-RW-RW----1 MySQL mysql 5 14:54 mysql-bin.000001

-RW-RW----1 MySQL mysql 106 Jul 5 14:54 mysql-bin.000002

-RW-RW----1 MySQL mysql 5 14:54 mysql-bin.index

-RW-RW----1 mysql mysql 19K Jul 5 14:54 Mysqld.log

-RW-RW----1 mysql mysql 12K Jul 4 18:27 mysqld.log-old

-RW-RW----1 MySQL mysql 5 Jul 5 14:54 mysqld.pid

-RW-RW----1 MySQL mysql 156 Jul 5 14:54 mysqld-relay-bin.000003

-RW-RW----1 mysql mysql 251 Jul 5 14:54 mysqld-relay-bin.000004

-RW-RW----1 MySQL mysql 5 14:54 mysqld-relay-bin.index

srwxrwxrwx 1 mysql mysql 0 Jul 5 14:54 mysqld.sock

-RW-RW----1 mysql mysql 2.8K Jul 5 14:54 Mysql-slow.log

-RW-RW----1 MySQL mysql 5 14:54 relay-log.info

drwx------2 mysql mysql 4.0K Jul 4 10:48 test

MySQL default is to generate a superuser named root, and no password, security is not good, so first set the MySQL native Super user password:

MySQL--defaults-file=/home/mysql/mysql/my.cnf-u root

mysql> Set Password = password (' xxxxxx ');

At this point, the MySQL startup is complete, in case of an exception to see the error log file Mysqld.log, which records the details of MySQL during the operation, similar to the ALTER log file in Oracle. Do not perform rm-rf/home/mysql/mysql operation, for the online environment, as far as possible to allow the fewest people to use the root account and MySQL account, try to operate under their own account (add their own account to the MySQL user group can do most of the operation).

This article is from the "Davideylee" blog, make sure to keep this source http://davideylee.blog.51cto.com/8703117/1530105