MARIADB Installation and Configuration

MARIADB Installation and Configuration

This article is based on the CentOS7.2 system to perform MARIADB installation and configuration, please close SELinux before installation and open 3306 ports in iptables rules, in this time we have directly emptied the iptables rules.

Iptables–fvim/etc/selinux/config #SELINUX =enforcing # #注释掉此项 # # #SELINUXTYPE =targeted # #注释掉此项 # # Selinux=disabled # #添加此项 # #

First, MARIADB installation 1, using Yum installation mariadb

Add the mariadb yum source, where Ali is used:

Get Http://mirrors.aliyun.com/repo/Centos-7.repo/etc/yum.repos.d/yum Install Mariadb-server mariadb-client # # Install MARIADB server side, client # #vim/ETC/MY.CNF.D/SERVER.CNF # #mariadb服务器端配置文件 # # [mysqld] # #以下项是优化项目可根据需要选择 # # Innodb_fi Le_per_table=on # #使用独立表空间模式 (recommended) # # Skip_name_resolve=on # #禁止域名反向解析 (optional) # #systemctl start Mariadb.service # #启动 MARIADB Service Systemctl Enable Mariadb.service # #设置为开机启动 # #mysql_secure_installation # #安装完成后安全初始化, add root password, delete anonymous login account , disable remote login with root user, delete test database and use # # mysql–uroot–hlocalhost–p ' PASSWORD # #登陆数据库 # # '

2. Install the MARIADB with the binary compression package

MARIADB binary Compression package named format: mariadb-version name-operating system name. tar.gz, make sure to download the correct version for your machine from the download as well.

Official download point: https://downloads.mariadb.org/, here we will use the mariadb5.5 version.

Extract the binary package to a directory and then the MySQL install DB installationscript.

We install mariadb into the directory/usr/local/mysql (which is the default installation directory in many platforms), but it is also possible to install any other directory.

We create a soft link for it, which makes it easy to change soft links and use different versions of MARIADB in different directories.

Note: for MARIADB 5.1.32 Just change the line "./scripts/mysqlInstalldb--user=mysql" to "./bin/mysqlInstalldb--user= MySQL "on the line.

Make sure you are using the correct my.cnf file

MARIADB look for configuration files in the '/etc/my.cnf ' location (some systems are '/etc/mysql/my.cnf ' or ' ~/.my.cnf '). If you have an older MY.CNF file in your system (the system has installed MARIADB or MySQL), you should note that the old configuration file was used after installing mariadb with the binary. Tar package. The normal workaround is not to use the configuration file in the directory, but to use the provisioning file in the binary package. This is done by creating a. my.cnf profile in your root directory, and then configuring MySQLinstalldb, mysqld_ with the option '--defaults-file=~/.my.cnf ' Safe and MySQL (command line tools) use only your own configuration files. Note that this option must be the first option of a command.

Install MARIADB in/usr/local/mysql with the root account

If you have root access to the system, you may use the "MySQL" User (group) to install MARIADB (to maintain compatibility with MySQL):

Groupadd mysqluseradd-g MySQL mysqlcd/usr/localtar-zxvpf/path-to/mariadb-version-os.tar.gzln-s mariadb-VERSION-OS MYSQLCD mysql./scripts/mysql_install_db--user=mysqlchown-r root. chown-r MySQL Data

It is recommended to use directive ln-s to create a soft link that facilitates testing, upgrading, downgrading, and so on, for installing multiple mariadb versions.

If you are using MARIADB instead of shutting down MySQL, you can simply call the MySQLinstallDB Command and MARIADB will find the data directory through your original/etc/my.cnf file (the path will vary depending on your system).

You now need to execute the MYSQLD directive as follows:

./bin/mysqld_safe--user=mysql &or./bin/mysqld_safe--defaults-file=~/.my.cnf--user=mysql &

Modify your $path so that you can call MySQL, mysqldump, and so on for connection testing.

Export path= $PATH:/usr/local/mysql/bin/

You may prefer to permanently save the settings by modifying the. bashrc or. bash_profile file.

Install mariadb in any directory with the root account

The following action changes the directory/usr/local to the directory of your choice:

Cd/usr/localgunzip </path-to/mariadb-version-os.tar.gz | Tar xf-ln-s mariadb-version-os mysqlcd mysql./scripts/mysql_install_db--defaults-file=~/.my.cnf

If you do not use the Gunzip command above, if you have GNU tar, you can do the same:

Tar xfz/path-to/mariadb-version-os.tar.gz

You need to start mysqld with the following instructions:

./bin/mysqld_safe--DEFAULTS-FILE=~/.MY.CNF &

Auto Start mysqld

You can copy the file Mysql.server to the appropriate location to make the mysqld (MARIADB server) automatic.

CP Support-files/mysql.server/etc/init.d/mysql.server

The exact location depends on your system, and the Mysql.server file contains instructions on how to use and optimize it.

Post-installation work

After that, remember to set the correct password for all your non-trusted connections to reduce your security risk! You can also consider using Mysql.server to enable MARIADB to start automatically when the system starts.

Second, the configuration of MARIADB

MARIADB uses an INI-style configuration file that provides configuration for multiple programs at the same time, mariadb reads multiple configuration files in multiple places, and is performed in the specified order; View commands for reading order:

My_print_defaultsdefault options is read from the following files in the given order:/etc/mysql/my.cnf/etc/my.cnf ~/.my. Cnf

/ETC/MY.CNF is generated by Mariadb-libs.

/ETC/MY.CNF.D/SERVER.CNF is generated by mariadb-server

/ETC/MY.CNF.D/CLIENT.CNF is generated by mariadb

When the same parameter appears in a different configuration file and has a different value, the post-read will be the final effective value.

To modify the default read configuration file (Mysqld_safe command):

--defaults-file=file_name

To load a file in addition to the default configuration file read:

--defaults-extra-file=path

Configuration file format: INI-style configuration file that provides configuration information for various MySQL applications:

[Mysqld] [Mysqld_safe] [Mysqld_multi] [Server] [MySQL] [Mysqldump] [Client] ... Configuration parameter format: PARAMETER = VALUE PARAMETER notation: innodb_file_per_table (recommended) innodb-file-per-table

Third, the MARIADB program documents and commands:

Server-side program: mysqldSafe, mysqldMulti client program: MySQL, mysqldump, mysqladmin Tools program: Myisampack, ...

1, MySQL: Interactive CLI tools, command-run;

mysql [options] db_name

Common options:

--host=host_name,-H host_name: server address;--user=user_name,-u user_name: User name;--password[=password],-p[password]: User password;-- Port=port_num,-P port_num: Server port;--protocol={tcp| Socket| pipe| MEMORY}: Local communication: Request based on local loopback address, Linux:socket windows:pipe,memory non-local communication: request with non-local loopback address, TCP protocol,--socke T=path,-S path--database=db_name,-D db_name:--compress,-C: Data compression transfer--execute=statement,-e statement: Non-interactive mode Execute SQL statement ;--vertical,-e: Query results are displayed vertically;

Command:

Client command: Execute on client;

server command: SQL statement, It needs to be sent to the server at once, and the statement must be terminator;

?          (\?)  Synonym for  ' help '.  # #使用帮助 # #clear       (\c)  clear the  current input statement.# #清除已输入的 # #connect     (\ r)  Reconnect to  the server. optional arguments are db and host.delimiter  (\d)  Set  statement delimiter.# #设置语句分隔符 # #edit        (\e)  edit command  with  $EDITOR .ego        (\g)  Send command to  mysql server, display result vertically.exit       (\q)   exit mysql. same as quit.# #退出 # #go          (\g)  send command to mysql server. #发送命令到mysql服务器 #help       (\h)  display this help.# #显示帮助 # #nopager    (\ n)  Disable pager, print to stdout.notee      (\ t)  don ' t write into outfile.# #不写入输出文件 # #pager     (\p)  set pager [to_pager] . print the query results via pager.print      (\p)  print current command.# #打印现在的命令 # #prompt      (\ r)  change your  mysql prompt.quit       (\q)  quit mysql.  # #退出 # # rehash     (\#)  rebuild completion hash.# #重新hash # #source       (\.)  execute an sql script file. takes a file name as an  argument. # #执行一个SQL脚本文件status      (\s)  get status information  from the server.# #从服务器获取状态信息 # #system      (\!)  execute a system shell command.tee      (\ t)  Set outfile [to_outfile]. Append  everything into given outfile.use        (\u)  Use  another database. # #使用其他数据库 # #charset     (\c)  Switch to another  charset. might be needed for processing binlog with multi-byte  charsets.# #切换到另一个字符集 # #warnings    (\w)  Show warnings after every  statement.nowarning  (\w)  don ' t show warnings after every  Use of the Statement.mysql command help: Man mysqlmysql  --help  --verbose

SQL script runs:

mysql [options] [DATABASE] </path/from/some_sql_script

MYSQLD Server program: How work features are defined: command line Options profile parameters

Server Parameters/variables: Set the operating characteristics of MySQL;

Mysql> SHOW [GLOBAL | SESSION] VARIABLES [Like_or_where];

Status (statistics) parameters/variables: Save statistics or status data during MySQL run;

Mysql> SHOW [GLOBAL | SESSION] STATUS [Like_or_where];

How to display a single variable setting value:

Mysql> SELECT @@[global.| Session.] System_var_name%: Matches any character of any length; _: matches any single character;

Variable/parameter level:

Global: Set default for all sessions;

Session: Related to a single session, and session creation is inherited from the global;

How server variables are adjusted: Runtime modification: Global: Only valid for newly established sessions after modification; session: Valid only for the current session and effective immediately; Modified by the configuration file: after the restart;

How to modify server variable values at run time:

mysql> help Setset [GLOBAL | SESSION] System_var_name = exprset [@ @global. | @ @session. | @@]system_var_name = Expr

Security initialization after installation is complete:

Mysql_secure_installation

Parameters that are often modified before running:

Innodb_file_per_table=on # #开始独立表空间模式 # #skip_name_resolve =on # #禁止域名反向解析 # #sql_safe_updates =on # #开启安全更新模式, cannot be set directly in the config file Righteousness # # ... Not to be continued .....

MARIADB Installation and Configuration