Archive

Articles taggués ‘database’

How To Optimize WordPress Performance With MySQL Replication On Ubuntu 14.04

11/02/2019 Aucun commentaire

Introduction

In this tutorial, we will teach you how to scale up your WordPress MySQL database server setup using master-slave database replication and the HyperDB plugin for WordPress. Adding more database servers to your environment in this manner allows your WordPress application to read from multiple database servers, increasing read performance.

MySQL replication reaps the most performance benefits for a system that processes frequent reads and infrequent writes, like most WordPress installations. By using a single-master with multiple-slave setup, you can add more slaves to scale your system, until you run out of network bandwidth or your master cannot handle the update load. If you wish, you can add more than one slaves by repeating the « slave » portions of the replication sections of this tutorial.

We are assuming that your setup includes two load balanced WordPress application servers that connect to a separate MySQL database server (see the prerequisites for a tutorial on how to set that up). It is not strictly necessary to have load balanced application servers to follow this tutorial, but your MySQL database server should be separate from your application servers.

Prerequisites

Before continuing with this tutorial, you should have completed two tutorials or have a similar environment:

After following those tutorials, to set up WordPress with two load balanced web application servers and a separate database server, you should have four VPSs. Because we will be dealing with several VPSs, for reference purposes, we will call your four existing VPSs the following:

  • haproxy-www: Your HAProxy server for layer 4 load balancing your WordPress web application servers. This is the entry point into your website
  • wordpress-1: Your first WordPress web application server
  • wordpress-2: Your second WordPress web application server
  • mysql-1: Your MySQL server for WordPress

That is, your environment should look something like this:

WordPress and Separate MySQL Database Server

In addition to your current environment, we will require one additional VPS during this tutorial. We will call it:

  • mysql-2: Your slave MySQL database server

Lire la suite…

How to log in to MySQL server without password

03/02/2019 Comments off

mysql without passwordIn order to log in to a MySQL server, you can run mysql command along with your login credentials and server’s IP address as arguments. For example:

$ mysql -u $MYSQL_ROOT -p $MYSQL_PASS -h 192.168.10.1

However, besides the inconvenience of typing extra arguments, using plain-text login credentials in a command line like above is really not a secure way to access a MySQL server. In a multi-user Linux environment, what you type in command line can easily be revealed to others who happen to run ps on the same host at the same time.

MySQL offers a way for you to log in to MySQL server without password, by using an external MySQL configuration file. In Linux, there are two different kinds of MySQL configuration files: (1) /etc/my.cnf and (2) ~/.my.conf. While any system-wide MySQL configuration is defined in /etc/my.cnf, any user-specific MySQL configuration is stored in ~/.my.cnf. You can leverage ~/.my.cnf, to define your MySQL login credential in the file.

$ vi ~/.my.cnf
[client]
user=alice
password=alice_passwd
host=192.168.10.1

Make sure to have the configuration file readable to you only.

$ chmod 0600 ~/.my.cnf

Once ~/.my.cnf is created, simply typing mysql command will let you log in to 192.168.10.1 as alice, and you no longer need to provide login password separately.

Source: Xmodulo

MySQL Cluster Replication: Multi-Master and Circular Replication

28/01/2019 Comments off

mysql-multi-master-replication-14-638Beginning with MySQL 5.1.18, it is possible to use MySQL Cluster in multi-master replication, including circular replication between a number of MySQL Clusters.

Prior to MySQL 5.1.18, multi-master replication including circular replication was not supported with MySQL Cluster replication. This was because log events created in a particular MySQL Cluster were wrongly tagged with the server ID of the master rather than the server ID of the originating server.

Circular replication example. In the next few paragraphs we consider the example of a replication setup involving three MySQL Clusters numbered 1, 2, and 3, in which Cluster 1 acts as the replication master for Cluster 2, Cluster 2 acts as the master for Cluster 3, and Cluster 3 acts as the master for Cluster 1. Each cluster has two SQL nodes, with SQL nodes A and B belonging to Cluster 1, SQL nodes C and D belonging to Cluster 2, and SQL nodes E and F belonging to Cluster 3.

Circular replication using these clusters is supported as long as the following conditions are met:

  • The SQL nodes on all masters and slaves are the same
  • All SQL nodes acting as replication masters and slaves are started using the --log-slave-updates option

Lire la suite…

mysqldump: Copy Database Using Shell Pipes

22/01/2019 Comments off

Source: nixCraft

How do I use mysqldump command with Unix shell pipes?

The mysqldump client is a backup program which is used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format. The syntax is as follows to dump a database called foo to the remote server called server1.cyberciti.biz:

mysqldump db-name | mysql -h server1.dbsysnet.com db-name
mysqldump foo | mysql -h server1.dbsysnet.com foo

However, I recommend using VPN or ssh client to secure data transfer using the shell pipes as follows:

mysqldump db-name | ssh user@server1.dbsysnet.com mysql db-name
mysqldump -u vivek -p'myPassWord' foo | ssh vivek@server1.dbsysnet.com mysql -u backup -p'myPassword' bar

 

Categories: Bases de données Tags: ,

Check The Number Of MySQL Open Database Connections on Linux Or Unix-like Server

21/01/2019 Comments off

Source: nixCraft

I‘m a new MySQL server user. My server is running on a CentOS Linux. How can I check the number of active MySQL connections on Linux based system?

You can use the following commands on Linux or Unix-like systems:

  • mysqladmin status command
  • MySQL show status command
  • netstat or ss commands

mysqladmin status command example

Open the terminal App or login to the remote server using ssh:

ssh mandrake@dbsysnet.com

Type the following command to get a short status message from the MySQL server:

mysqladmin status
## OR ##
mysqladmin status -u root -p
## OR ##
mysqladmin status -h db1.dbsysnet.com -u root -p

Sample outputs:

Uptime: 691356  Threads: 5  Questions: 83237956  Slow queries: 102736  Opens: 3585  Flush tables: 1  Open tables: 1019  Queries per second avg: 120.398

MySQL show status command to see open database connections example

First, connect to the your mysql server:

mysql -u root -p

Type the following sql query to see the number of connection attempts to the MySQL serverincludes both failed and successful connection attempts:

mysql> show status like 'Conn%';

Sample outputs:

show-status-like-Conn
You can use the following sql command to see the number of currently open connections at mysql> prompt:

mysql> show status like '%onn%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| Aborted_connects         | 7       |
| Connections              | 6304067 |
| Max_used_connections     | 85      |
| Ssl_client_connects      | 0       |
| Ssl_connect_renegotiates | 0       |
| Ssl_finished_connects    | 0       |
| Threads_connected        | 7       | <---- No of currently open connections
+--------------------------+---------+
7 rows in set (0.00 sec)

Lire la suite…

Categories: Bases de données Tags: , ,