MySQL server (i)

MySQL server

1. mysql Installation

Version: mysql-advanced-5.6.23-linux-glibc2.5-x86_64

Refer to the Install-binary file in the directory for details

#安装mysql, unzip the MySQL compression to/usr/local

Tar zxvf mysql-advanced-5.6.23-linux-glibc2.5-x86_64.tar.gz-c/uar/local/mysql

Cd/usr/local

Ln-s mysql-advanced-5.6.23-linux-glibc2.5-x86_64 MySQL

CD MySQL

#添加mysql用户和群组

Groupadd MySQL

Useradd-r-G MySQL MySQL

#修改相关目录和文件属主和所属群组

Chown-r MySQL.

Chgrp-r MySQL.

Or: Chown-r mysql:mysql.

#将目录属主更改为root to prevent MySQL users from deleting directories

Chown-r Root.

Chown-r MySQL Data

#使用mysql用户权限来初始化数据库

scripts/mysql_install_db--user=mysql--datadir=/data--keep-my-cnf--no-defaults

#会提示缺少perl-modules

#FATAL Error:please Install the following Perl modules before executing scripts/mysql_install_db

Yum-y Install Perl-module-install.noarch

#缺少依赖包libaio

#error while loading shared libraries:libaio.so.1:cannot open Shared object file:no such file or directory

Yum-y Install Libaio-devel

#将mysql添加为服务

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

Chkconfig--add mysqld

#将mysql的bin目录加入到PATH环境变量

#在/etc/profile The last side to add

Path= $PATH:/usr/local/mysql/bin

Source/etc/profile

#脚本初始化已经添加mysqld脚本, if there is no direct cp/etc/init.d/mysqld

/etc/rc.d/init.d/mysqld

#启动mysql

/usr/local/mysql/bin/mysqld_safe &

#更改mysql root password hint

#error: ' Can ' t connect to local MySQL server through socket '/tmp/mysql.sock '

/usr/local/mysqladmin-u root-p Password--socket=/var/lib/mysql/mysql.sock

#或者

Ln-s/var/lib/mysql/mysql.sock/tmp/mysql.sock

#在/etc/my.cnf File The socket parameter is specified as/tmp/mysql.sock

Socket=/tmp/mysql.sock

#启动mysql

Mysqld_safe--defaults-file=/etc/my.cnf--datadir=/data--pid-file=/tmp/mysql.pid&

#查看mysql启动情况

PS aux | grep MySQL

#查看mysql是否在监听端口3306

Lsof i:3306

#

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/12/wKiom1Vytx6AV8JrAAE3nOWUY38452.jpg "/>

#mysql已经启动成功

2. Basic Configuration

#待补充

3. Database backup and restore

(1) Add copy user from server

#给192.168.137.31 This host synchronizes replication from the primary server with username slave and password love

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6E/0E/wKioL1VyuL7Cnf0kAABr4rDcOR0053.jpg "/>

#远程连接错误

#ERROR 2003 (HY000): Can ' t connect to MySQL server on ' 192.168.137.30 ' (113)

#查看错误113

Perrot 113

#由于启用防火墙所致, Centos7 Firewall is FIREWALLD by default

Systemctl Stop Firewalld.service

Systemctl Disable FIREWALLD

#关闭开启防火墙前后650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6E/12/wKiom1Vytx_g9eMvAAEeH4H7c7I649.jpg "/ >

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6E/0E/wKioL1VyuL-yJImnAAFn0oynl0I700.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6E/12/wKiom1VytyCSrzedAAIRD0wfji4256.jpg "/>

(2) Full backup and restore

#默认数据库:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/0E/wKioL1VyuMCy3cYRAACSBBkK7EA346.jpg "/>

#创建数据库d1和d2以及表t1和t2.

Create Database D1;

Create Database D2;

Use D1;

CREATE TABLE T1 (ID int,name char (50));

INSERT into T1 value (1, ' t1name1 ');

INSERT into T1 value (2, ' t1name2 ');

Use D2;

CREATE TABLE t2 (ID int,name char (50));

INSERT into T2 value (1, ' t2name1 ');

INSERT into T2 value (2, ' t1name2 ');

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/12/wKiom1VytyDjv7vJAACY3SJGggA122.jpg "/>

#备份数据库d1和d2表数据,-l locks all tables, the backup is automatically unlocked, and the-f backup completes to regenerate the Binlog log:

Mysqldump-l-f-u root-p D1 >data/d1.sql

Mysqldump-l-f-u root-p D2 >data/d2.sql

#备份所有数据库, including the database that comes with it:

Mysqldump--all-databases-l-f-u root-p >data/all.sql

#还原到从服务器:

SCP Data/d*.sql [Email protected]:/usr/local/mysql/data

#在从服务器执行:

Mysql-u root-p

Create Database D1;

Create Database D2;

Use D1;

Source/usr/local/mysql/data/d1.sql

Use D2;

Source/usr/local/mysql/data/d2.sql

(3) binlog Restore data

#待补充

4, master-slave replication

(1) parameter not supported from version 5.1.7

#5. Version 1.7 does not support parameters such as Master-host, Master-user, Master-password, etc., when configuring from the server, you need to do it from the server:

Change Master to master_host= ' master server IP ', master_user= ' sync using user name (slave) ', master_password= ' sync user corresponding password (love) ';

Start slave;

(2) Binlog log file operation

#重新生成binlog日志, if the current Binlog log is mysql-bin.000004, the newly generated binlog log is mysql-bin.000005

Flush logs;

#重置binlog日志, that is, the Binlog log starts at the origin, such as double from mysql-bin.000001.

Reset Master;

#查看slave状态

show slave status\g;

#可以使用mysqlbinlog来查看binlog日志

mysqlbinlog/data/mysql-bin.000004

(3) Configuring the Slave server

#安装方法与主服务器一样, do not repeat the instructions.

#修改root密码

Mysqladmin-u root-p Password

Change Master to master_host= ' 192.168.137.30 ', master_user= ' slave ', master_password= ' love ';

Start slave;

show slave status\g;

#由于之前做了从服务器中复制 and reset the primary and Binlog logs from the server, the following actions are required

#在主服务器重置binlog日志, production environment do not simply reset the Binlog log, and the reset will delete all log files after the starting point

#为方便查看同步效果, if the primary server has been manually established database, it should be deleted, if not deleted and not manually established

#数据库备份还原到从服务器, when the primary server on these databases to delete and repair, from the server because there is no such database, will be error

#将测试数据库删除, and reset the log file

drop database D1;

Drop database D2;

Reset Master;

#查看主服务器log_pos位置起点 to set the synchronization starting point from the server,

Show master status;

#起点变成120

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6E/0E/wKioL1VyuMCSmQv7AADW5rvOKU8464.jpg "/>

#从服务器

#配置文件

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6E/12/wKiom1VytyKRJQAZAAHB8Oc9Og0129.jpg "/>

SERVER_ID: Must be unique

REPLICATE-DO-DB: Specifies the database to be synchronized, one row for the database

REPLICATE-IGNORE-DB: Specify a database that does not need to be synchronized, one row for the database

#重置从服务器日志并开启复制

Reset Master;

Stop slave;

Change Master to master_host= ' 192.168.137.30 ', master_user= ' slave ', master_password= ' love ';

Start slave;

#看到Slave_IO_Running和Slave_SQL_Running状态显示为Yes, you can see that the connection to the primary server is normal

Slave_io_running: is responsible for copying the binlog from the primary server to local, yes indicates that the replication status is normal, no indicates that the replication status is not normal or cannot be replicated from the primary server.

Slave_sql_running: is responsible for reading Positon points and SQL statements from the Binlog log copied to the local and executing them again, and writes its own operation log to the Relaylog relay log.

Slave_sql_running_state:slave have read all relay log; Waiting for the slave I/O thread to update it

#从服务器正等待主服务器进行增, delete, repair operation, once the primary server to do these operations, from the server during the refresh time will be based on the Binlog from the primary server to synchronize data

#Replicate_Ignore_DB显示的是忽略同步的数据库

Replicate_ignore_db:mysql,test,information_schema,ignoredb

#需要在my. CNF configuration file Add, one row to specify a database

Replicate-ignore-db=mysql

Replicate-ignore-db=test

Replicate-ignore-db=information_schema

Replicate-ignore-db=ignoredb

#将复制事件写入到自己的二进制日志文件, the trunk log and its own Binlog log default value are turned on and stored in/var/lib/mysql

Log-slave-updates

#数据库存放位置可以通过datadir参数来更改

Datadir=/data

#日志存放位置可以通过log-bin parameter plus absolute path to modify

Log-bin=/binlog/mysql-bin

#如果在启动过mysql之后再修改数据目录存放路径, the PID file, the trunk log file and the database file to the modified directory first, or the startup MySQL always prompt to find the PID file, and can not start successfully, generally will be binlog files and database files are separated, Avoid disk corruption and inability to recover data using Binlog

#在主服务器上重新建立数据库d1和d2以及t1和t2, you can see the two databases in sync from the server soon.

This article is from the "Love On Action" blog, please be sure to keep this source http://1055745601.blog.51cto.com/5003160/1659226

MySQL server (i)