Shulou(Shulou.com)06/01 Report--

One version.

Mysql database version: Preconna Server, MariaDB or MYSQL

Three storage engines of 1.mysql

Mysql provides two storage engines, MyISAM,InnoDB.xtraDB

MyISAM does not have log and thing support. So the performance of Ibank 0 is very good.

InnoDB has log support for transactions. (through logging, it is easy to recover. Enhances the robustness of mysql) the mysql5.5 version defaults to InnoDB

XtraDB is an enhanced version of the InnoDB storage engine that provides support for higher-performance computers.

2.Percona Server branch

A version of mysql released by Percona Inc. (mysql Consulting). Provide xtraDB engine, with Percona-toolkit and other management tools.

But percona Server is closed source.

3.MariaDB

The MariaDB10.0.9 version uses the xtraDB engine. There is an open source version developed by the founders of mysql. Avoid the risk of Oracle shutting down its mysql.

Two mysql command operation

1. Connect mysql

# cd / usr/local/mysql

Tips; hide passwords. Enter the input below # HISTCONTROL=ignorespace at the command line. Always enter the space first and the content will not be recorded.

#. / bin/mysql-h hostname-u username-p password-h hostname-A (library name can be switched)

#. / bin/mysql-uroot-p

Mysql > show dattabases; (view database name)

Mysql > use database (switch database)

Mysql > show tables (view the table of the database library)

Mysql > desc table (view the structure of the table)

Mysql > create database name (create library)

Mysql > create table swa (an int,b varchar); (create table swa)

Mysql > desc swa

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | a | int (11) | YES | | NULL |

| | b | varchar | YES | | NULL |

+-+ +

Mysql > alter table swa add id int (4) default '0fields; (add fields in the table)

Mysql > rename table original table name to new table name; (modify the name of the table)

Mysql > insert into swa values (11); (insert data)

Mysql > select * from swa

+-+ +

| | a | b | |

+-+ +

| | 11 | aasdfsdfsf |

+-+ +

Mysql > insert into swa values (1214424); (write to swa table)

Mysql > select * from swa where axi11

+-+ +

| | a | b | |

+-+ +

| | 11 | aasdfsdfsf |

+-+ +

Mysql > select * from swa where aura 12

+-+ +

| | a | b | |

+-+ +

| | 12 | 123424 | |

+-+ +

Mysql > drop table name; (delete table)

Mysql > drop database name; (delete library)

If you delete a database that does not exist, an error will be reported. Solution:

Mysql > drop database if exists name; (determine whether there is a database after database)

two。 Change the password: use the / bin/mysqladmin command under shell (not in mysql):

Format mysqladmin-u username-p old password password new password

#. / bin/mysqladmin-uroot-proot password root123

Warning: Using a password on the command line interface can be insecure.

3. Add new users / authorized users

Format grant permissions on database. Data table to user name @ login host identified by 'password'

If the login host is set to%, any host can log in, (very dangerous)

Mysql > grant select,insert on swa.* to code@localhost identified by '123'

Delete the user; (principle: delete the user table data under the mysql library)

Mysql > drop user code@localhost

4. Backup database: use the / bin/mysqldump command under shell (not in mysql)

#. / bin/mysqldump-u user name-p database name > path .sql

#. / bin/mysqldump-u user name-p database name table name > path .sql (backup table)

5. Restore the database.

1. Under the shell command line:

. / bin/mysql-u user name-p-A database name

< 路径.sql 2.进入数据库 mysql>

Use swa

Mysql > source path .sql

Three mysql's high-availability architecture mysql master-slave replication.

123as master library mysql:DB1 124as slave library mysql:DB2

Configuration of DB1:

Default profile / etc/my.cnf

[mysqld]

Server-id = 1

Log-bin=mysql-bin

Relay-log = mysql-relay-bin

Replicate-wild-ignore-table=mysql.%

Replicate-wild-ignore-table=test.%

Replicate-wild-ignore-table=information_schema.%

A brief analysis of the meaning:

Server-id = 1 # node identifier, master and slave should be inconsistent

Naming format of log-bin=mysql-bin # binaries

Relay-log = mysql-relay-bin # format for relaying logs from the server

Replicate-wild-ignore-table # can ignore synchronized database names

The configuration of DB2:slave is also in the [mysqld] field of / etc/my.cnf.

# slave#

Server_id = 2

# log-bin=mysql-bin

Relay-log = mysql-relay-bin

Replicate-wild-ignore-table=mysql.%

Replicate-wild-ignore-table=test.%

Replicate-wild-ignore-table=information_schema.%

# #

Configure master and slave

1. Synchronize data:

DB1:mysql > FLUSH TABLES WITH READ LOCK; # Lock table

Unlocking UNLOCK TABLES;#

Copy the data from DB1 to the location of DB2.

two。 Grant permissions to DB2 on DB1:

Mysql > grant replication slave on *. * to 'rep_user'@'192.168.1.124' identified by' rep_user'

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-bin.000001 | 334 | |

+-+

3. Configure slave on DB2 and turn on slave

Mysql > change master to master_host='192.168.1.123',master_user='rep_user',master_password='rep_user',master_log_file='mysql-bin.000001',master_log_pos=334

Mysql > start slave

Error message:

Last_SQL_Error: Error 'Can't drop database' swa1'; database doesn't exist' on query. Default database: 'swa1'. Query: 'drop database swa1'

DB2: mysql > stop slave

Mysql > set global sql_slave_skip_counter=1

Msyql > start slave

Or you can do it the other way around. DB2 is dominant. DB1 is from. The my.cnf of DB2 enables log-bin logs. Give DB1 permission on DB2. DB1 configuration slave information.

If an error occurs, please check a few places:

Change master to master_host='192.168.1.123',master_user='rep_user',master_password='rep_user',master_log_file='mysql-bin.000001',master_log_pos=334

All the parameters in this statement are "," concatenated. And don't write these parameters wrong.

On the basis of these, check whether DB1 and DB2 can communicate with ping. Check to see if port 3306 is open. Turn off the security mechanism if it doesn't match.

To achieve high availability: install keepalived to achieve high availability

Download software: wget http://www.keepalived.org/software/keepalived-1.2.12.tar.gz

Install the software:

Tar xf

Cd keepalived-1.2.12

. / configure-- sysconf=/etc-- with-kernel-dir=/usr/src/kernels/2.6.32-431.5.1.el6.x86_64

Error message:!! Can not include OpenSSL headers files.

Configure: error:! OpenSSL is not properly installed on your system

Solution: # yum install openssl-devel perl-DBI perl-DBD-MySQL-y

(my monitoring script is in perl language, so I installed the perl extension module.)

. / configure-- sysconf=/etc-- with-kernel-dir=/usr/src/kernels/2.6.32-431.5.1.el6.x86_64

Make & & make install

Two machines do keepalived:

123:master 124 backup

Configuration of 123

Vim keepalived.conf

Global_defs {

Notification_email {

Acassen@firewall.loc

Failover@firewall.loc

Sysadmin@firewall.loc

}

Notification_email_from Alexandre.Cassen@firewall.loc

Smtp_server 127.0.0.1

Smtp_connect_timeout 30

Router_id MySQLHA_DEVEL

}

Vrrp_script check_mysqld {

Script "/ etc/keepalived/mysqlcheck/check_slave.pl 127.0.0.1" # the script here must have execution permission

Interval 2

}

Vrrp_instance HA_1 {

State MASTER # if there is a problem with the host, it will switch to the standby. Wait until the mainframe is restored. Switch back to the mainframe.

Interface eth0

Virtual_router_id 80

Priority 100

Advert_int 2

Authentication {

Auth_type PASS

Auth_pass qweasdzxc

}

Track_script {

Check_mysqld

}

Virtual_ipaddress {

192.168.1.133/24 dev eth0

}

}

Configuration: vim keepalived.conf

Global_defs {

Notification_email {

Acassen@firewall.loc

Failover@firewall.loc

Sysadmin@firewall.loc

}

Notification_email_from Alexandre.Cassen@firewall.loc

Smtp_server 192.168.200.1

Smtp_connect_timeout 30

Router_id MySQLHA_DEVEL

}

Vrrp_script check_mysqld {

Script "/ etc/keepalived/mysqlcheck/check_slave.pl 127.0.0.1" # script to detect mysql replication status

Interval 2

# weight 21 # enabled if there are multiple BACKUP. 2 is not open. Easily lead to parsing errors

}

Vrrp_instance HA_1 {

State BACKUP # is configured as BACKUP on both DB1 and DB2

Interface eth0

Virtual_router_id 80

Priority 90 # the values here are different

Advert_int 2

# nopreempt # No preemption mode. It can only be set on machines with high priority, but not on machines with low priority.

Authentication {

Auth_type PASS

Auth_pass qweasdzxc

}

Track_script {

Check_mysqld

}

Virtual_ipaddress {

192.168.1.133 the external service IP of 24 dev eth0 # mysql, or VIP

}

}

Here's check_slave.pl.

#! / usr/bin/perl-w

Use DBI

Use DBD::mysql

# CONFIG VARIABLES

$SBM = 120

$db = ""

$host = $ARGV [0]

$port = 3306

$user = "root"

$pw = ""

# SQL query

$query = "show slave status"

$dbh = DBI- > connect ("DBI:mysql:$db:$host:$port", $user, $pw, {RaiseError = > 0MagnePrintError = > 0})

If (! defined ($dbh)) {

Exit 1

}

$sqlQuery = $dbh- > prepare ($query)

$sqlQuery- > execute

$Slave_IO_Running = ""

$Slave_SQL_Running = ""

$Seconds_Behind_Master = ""

While (my $ref = $sqlQuery- > fetchrow_hashref ()) {

$Slave_IO_Running = $ref- > {'Slave_IO_Running'}

$Slave_SQL_Running = $ref- > {'Slave_SQL_Running'}

$Seconds_Behind_Master = $ref- > {'Seconds_Behind_Master'}

}

$sqlQuery- > finish

$dbh- > disconnect ()

If ($Slave_IO_Running eq "No" | | $Slave_SQL_Running eq "No") {

Exit 1

} else {

If ($Seconds_Behind_Master > $SBM) {

Exit 1

} else {

Exit 0

}

}

The problem here is if the script does not have permission. Keepalived cannot start VIP, and this script requires perl extension support. The front is already yum.

Test; open another host with the same network segment.

Permissions are granted on 123 and 124, respectively.

# grant all privileges on *. * to 'root'@' newly opened host ip' identified by' root'

Log in on the newly opened host

. / bin/mysql-uroot-p-h 192.168.1.133

It should be noted here that-h and VIP address require the keepalived service to start successfully. Otherwise, you will not be able to generate VIP.

Mysql > show variables like'% hostname%'

Mysql > show variables like'% hostname%'

You can see which mysql machine is logged in.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.