Installing Nginx with PHP 7 and MySQL 5.7 (LEMP) on Ubuntu 16.04 LTS

Standard

From: https://www.howtoforge.com/tutorial/installing-nginx-with-php7-fpm-and-mysql-on-ubuntu-16.04-lts-lemp/


Nginx (pronounced “engine x”) is a free, open-source, high-performance HTTP server. Nginx is known for its stability, rich feature set, simple configuration, and low resource consumption. This tutorial shows how you can install Nginx on an Ubuntu 16.04 server with PHP 7 support (through PHP-FPM) and MySQL 5.7 support (LEMP = Linux + nginx (pronounced “engine x”) + MySQL + PHP).

 

1 Preliminary Note

In this tutorial, I use the hostname server1.example.com with the IP address 192.168.1.100. These settings might differ for you, so you have to replace them where appropriate.

I’m running all the steps in this tutorial with root privileges, so make sure you’re logged in as root:

sudo -s

 

2 Installing MySQL 5.7

In order to install MySQL, we run:

apt-get -y install mysql-server mysql-client

You will be asked to provide a password for the MySQL root user – this password is valid for the user root@localhost as well as root@server1.example.com, so we don’t have to specify a MySQL root password manually later on:

New password for the MySQL “root” user: <– yourrootsqlpassword
Repeat password for the MySQL “root” user: <– yourrootsqlpassword

To secure the database server and remove  the anonymous user and test database, run the mysql_secure_installation command.

mysql_secure_installation

You will be asked these questions:

root@server1:~# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root: <– Enter the MySQL root password

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: <– Press y if you want this function or press Enter otherwise.
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : <– Press enter

… skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : <– y
Success.

Normally, root should only be allowed to connect from
‘localhost’. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : <– y
Success.

By default, MySQL comes with a database named ‘test’ that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : <– y
– Dropping test database…
Success.

– Removing privileges on test database…
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : <– y
Success.

All done!

MySQL is secured now.

3 Installing Nginx

In case that you have installed Apache2 already, then remove it first with these commands & then install nginx:

service apache2 stop
update-rc.d -f apache2 remove
apt-get remove apache2

Nginx is available as a package for Ubuntu 16.04 which we can install.

apt-get -y install nginx

Start nginx afterwards:

service nginx start

Type in your web server’s IP address or hostname into a browser (e.g. http://192.168.1.100), and you should see the following page:

The Ubuntu Nginx default page.

The default nginx document root on Ubuntu 16.04 is /var/www/html.

 

4 Installing PHP 7

We can make PHP work in nginx through PHP-FPM (PHP-FPM (FastCGI Process Manager) is an alternative PHP FastCGI implementation with some additional features useful for sites of any size, especially busier sites) which we install as follows:

apt-get -y install php7.0-fpm

PHP-FPM is a daemon process (with the init script php7.0-fpm) that runs a FastCGI server on the socket /run/php/php7.0-fpm.sock.

 

5 Configuring nginx

The nginx configuration is in /etc/nginx/nginx.conf which we open now:

nano /etc/nginx/nginx.conf

The configuration is easy to understand (you can learn more about it here: http://wiki.nginx.org/NginxFullExample and here: http://wiki.nginx.org/NginxFullExample2)

First (this is optional) adjust the keepalive_timeout to a reasonable value:

The virtual hosts are defined in server {} containers. The default vhost is defined in the file /etc/nginx/sites-available/default – let’s modify it as follows:

nano /etc/nginx/sites-available/default

server_name _; makes this a default catchall vhost (of course, you can as well specify a hostname here like www.example.com).

root /var/www/html; means that the document root is the directory /var/www/html.

The important part for PHP is the location ~ \.php$ {} stanza. Uncomment it to enable it.

Now save the file and reload nginx:

service nginx reload

Next open /etc/php/7.0/fpm/php.ini

nano /etc/php/7.0/fpm/php.ini

… and set cgi.fix_pathinfo=0:

Reload PHP-FPM:

service php7.0-fpm reload

Now create the following PHP file in the document root /var/www/html:

nano /var/www/html/info.php

Now we call that file in a browser (e.g. http://192.168.1.100/info.php):

PHP Info on Ubuntu with Nginx.

As you see, PHP 7 is working, and it’s working through FPM/FastCGI, as shown in the Server API line. If you scroll further down, you will see all modules that are already enabled in PHP. MySQL is not listed there which means we don’t have MySQL support in PHP yet.

 

6 Getting MySQL Support In PHP 7

To get MySQL support in PHP, we can install the php7.0-mysql package. It’s a good idea to install some other PHP modules as well as you might need them for your applications. You can search for available PHP modules like this:

apt-cache search php7.0

Pick the ones you need and install them like this:

apt-get -y install php7.0-mysql php7.0-curl php7.0-gd php7.0-intl php-pear php-imagick php7.0-imap php7.0-mcrypt php-memcache  php7.0-pspell php7.0-recode php7.0-sqlite3 php7.0-tidy php7.0-xmlrpc php7.0-xsl php7.0-mbstring php-gettext

APCu is an extension for the PHP Opcache module that comes with PHP 7, it adds some compatibility features for software that supports the APC cache (e.g. WordPress cache plugins).

APCu can be installed as follows:

apt-get -y install php-apcu

Now reload PHP-FPM:

service php7.0-fpm reload

Now reload http://192.168.1.100/info.php in your browser and scroll down to the modules section again. You should now find lots of new modules there, including the MySQL module:

The PHP Modules have been installed.

 

7 Making PHP-FPM use a TCP Connection

By default PHP-FPM is listening on the socket /var/run/php/php7.0-fpm.sock. It is also possible to make PHP-FPM use a TCP connection. To do this, open /etc/php/7.0/fpm/pool.d/www.conf

nano /etc/php/7.0/fpm/pool.d/www.conf

… and make the listen line look as follows:

This will make PHP-FPM listen on port 9000 on the IP 127.0.0.1 (localhost). Make sure you use a port that is not in use on your system.

Then reload PHP-FPM:

php7.0-fpm reload

Next go through your nginx configuration and all your vhosts and change the line fastcgi_pass unix:/var/run/php/php7.0-fpm.sock; to fastcgi_pass 127.0.0.1:9000;, e.g. like this:

nano /etc/nginx/sites-available/default

Finally, reload nginx:

service nginx reload

That’s it. The Nginx LEMP server is installed.

128 total views, no views today

WordPress Portable for Windows

Standard

WP-Portable-3-3-1
WP-Portable-3-3-1
WP-Portable-3-3-1.zip
Version: 3.3.1
10.9 MiB
555 Downloads
Details...
From: http://wordpress-portable.webnode.com/


WordPress Portable is the package some have been waiting for. Just download it, extract on your local drive or flash drive and run it!
Possible uses for a portable WordPress
Trying new WordPress versions
Showcasing your work to clients
Theme development & testing
Plugin development & testing
Using WordPress Portable
To run WordPress Portable just start the file in the main folder and double-click the system tray icon to open your WordPress page. To quit WordPress Portable right-click the system tray icon and then click exit.

Read more: http://wordpress-portable.webnode.com/
Create your own website for free: http://www.webnode.com

2,555 total views, no views today

PHP script: Change all the table engine types for a given database

Standard

From: http://forums.mysql.com/read.php?21,26193,49429#msg-49429


487 total views, no views today

Stress test MySQL with mysqlslap

Standard

From: http://www.techrepublic.com/blog/howdoi/how-do-i-stress-test-mysql-with-mysqlslap/133


How do I… Stress test MySQL with mysqlslap?
By Melonfire
September 17, 2007, 12:12 AM PDT
Takeaway: The mysqlslap utility makes it possible to benchmark and compare MySQL performance on different hardware, as well as accurately quantify the effect of a change in database design. This tutorial shows how you can use mysqlslap to run tests involving multiple clients, custom queries, different table engines, and much more.

One of the interesting new tools in MySQL 5.1.4 is mysqlslap, a load emulator that lets you see how well a particular query set or table engine performs under high-load conditions.

A query that consumes too many database resources may be the result of designing tables incorrectly, choosing the wrong table type, or creating an inefficient query. When a query eats up a lot of database resources, it can negatively affect other application components. By using mysqlslap to stress test a server in a non-public environment, you will discover these errors sooner, allowing you to you avoid a database meltdown once your application goes live.

This tutorial shows how you can use mysqlslap to run stress tests involving multiple clients, custom queries, different table engines, and much more.

Basic usage

This simple (and unrealistic) example uses mysqlslap to test server performance assuming only one client connection:

shell> /usr/local/mysql/bin/mysqlslap –user=john –auto-generate-sql

Benchmark

Average number of seconds to run all queries: 0.006 seconds

Minimum number of seconds to run all queries: 0.006 seconds

Maximum number of seconds to run all queries: 0.006 seconds

Number of clients running queries: 1

Average number of queries per client: 0
The –auto-generate-sql switch tells mysqlslap to automatically generate and execute SQL statements, monitor how fast MySQL performs this task, and display the result. The results indicate that MySQL took 0.006 seconds to execute the SQL statements.

The –auto-generate-sql switch creates a table, executes an INSERT query and saves dummy data to it, executes a SELECT query to retrieve the dummy data, and then drops the table. You can see behind-the-scenes action by adding the -v switch to the mysqlslap command line (adding extra ‘v’s increases the verbosity level):

shell> /usr/local/mysql/bin/mysqlslap –user=john –auto-generate-sql -vv

DROP SCHEMA IF EXISTS `mysqlslap`;

CREATE SCHEMA `mysqlslap`;

CREATE SCHEMA `mysqlslap`;

CREATE TABLE `t1` (intcol1 INT(32),charcol1 VARCHAR(128));

INSERT INTO t1 VALUES (1804289383,’mxvtvmC9127qJNm06sGB8R92q2j7vTiiITRDGXM9ZLzkd

ekbWtmXKwZ2qG1llkRw5m9DHOFilEREk3q7oce8O3BEJC0woJsm6uzFAEynLH2xCsw1KQ1lT4zg9rdxB

L’);

SELECT intcol1,charcol1 FROM t1;

Benchmark

Average number of seconds to run all queries: 0.007 seconds

Minimum number of seconds to run all queries: 0.007 seconds

Maximum number of seconds to run all queries: 0.007 seconds

Number of clients running queries: 1

Average number of queries per client: 0 DROP SCHEMA IF EXISTS `mysqlslap`;
It’s unlikely that you’ll have only a single client connecting to the MySQL server at any given time, so you’ll typically also need the –concurrency switch, which lets you simulate multiple simultaneous client connections, like this:

shell> /usr/local/mysql/bin/mysqlslap –user=john –auto-generate-sql –concurrency=100

Benchmark

Average number of seconds to run all queries: 0.698 seconds

Minimum number of seconds to run all queries: 0.698 seconds

Maximum number of seconds to run all queries: 0.698 seconds

Number of clients running queries: 100

Average number of queries per client: 0
MySQL performance drops pretty significantly (from 0.007 seconds to 0.698 seconds) when it has to deal with 100 clients instead of just one.

See what happens if you increase the number of concurrent connections even more:

shell> /usr/local/mysql/bin/mysqlslap –user=john –auto-generate-sql –concurrency=300

Benchmark

Average number of seconds to run all queries: 47.515 seconds

Minimum number of seconds to run all queries: 47.515 seconds

Maximum number of seconds to run all queries: 47.515 seconds

Number of clients running queries: 300

Average number of queries per client: 0
Note: As you increase the number of concurrent connections, you might encounter a “Too many connections” error. You need to adjust MySQL’s ‘max_connections’ variable, which controls the maximum number of concurrent connections allowed by the server.

Running tests more than once

You can force mysqlslap to run a particular test more than once by adding the –iterations switch to the command line. This example runs the same test five times and prints a composite result:

shell> /usr/local/mysql/bin/mysqlslap –user=john –auto-generate-sql –concurrency=100 –iterations=5

Benchmark

Average number of seconds to run all queries: 0.714 seconds

Minimum number of seconds to run all queries: 0.682 seconds

Maximum number of seconds to run all queries: 0.753 seconds

Number of clients running queries: 100

Average number of queries per client: 0
Specifying the total number of queries

It’s possible to force each “client” to run a specific number of queries by adding the –number-of-queries switch to the mysqlslap command line. When mysqlslap encounters this switch, it divides the corresponding value by the number of concurrent connections and uses the result to decide how many queries each client should run.

For example, with settings of 500 total queries and five concurrent clients, mysqlslap will run 500/5 = 100 queries per client. Take a look at an example:

shell> /usr/local/mysql/bin/mysqlslap –user=john –auto-generate-sql –concurrency=100 –number-of-queries=10000

Benchmark

Average number of seconds to run all queries: 0.694 seconds

Minimum number of seconds to run all queries: 0.694 seconds

Maximum number of seconds to run all queries: 0.694 seconds

Number of clients running queries: 100

Average number of queries per client: 100
Using larger tables

The default behavior of mysqlslap when using the –auto-generate-sql switch is to create a two-column table with one integer column and one character column. If this isn’t representative of the kind of tables you typically use, you can adjust these settings to include more integer and/or character columns, with the –number-char-cols and –number-int-cols switches. Here are examples:

shell> /usr/local/mysql/bin/mysqlslap –user=john –auto-generate-sql –concurrency=100 –number-of-queries=1000 –number-char-cols=4 –number-int-cols=7

Benchmark

Average number of seconds to run all queries: 1.290 seconds

Minimum number of seconds to run all queries: 1.290 seconds

Maximum number of seconds to run all queries: 1.290 seconds

Number of clients running queries: 100

Average number of queries per client: 10 shell> /usr/local/mysql/bin/mysqlslap –user=john –auto-generate-sql –concurrency=100 –number-char-cols=4

Benchmark

Average number of seconds to run all queries: 0.968 seconds

Minimum number of seconds to run all queries: 0.968 seconds

Maximum number of seconds to run all queries: 0.968 seconds

Number of clients running queries: 100

Average number of queries per client: 0

shell> /usr/local/mysql/bin/mysqlslap –user=john –auto-generate-sql –concurrency=100 –number-int-cols=5

Benchmark

Average number of seconds to run all queries: 1.076 seconds

Minimum number of seconds to run all queries: 1.076 seconds

Maximum number of seconds to run all queries: 1.076 seconds

Number of clients running queries: 100

Average number of queries per client: 0
Using custom queries

While the –auto-generate-sql option is fine for general load testing, you may want to test the performance of a specific query on a database that already exists. In these situations, you can bypass the –auto-generate-sql switch and instead tell mysqlslap to use your own custom query with the –query switch. Here’s the next example:

shell> /usr/local/mysql/bin/mysqlslap –user=john –create-schema=world –query=”SELECT City.Name, City.District FROM City, Country WHERE City.CountryCode = Country.Code AND Country.Code = ‘IND’;” –concurrency=100 –iterations=5

Benchmark

Average number of seconds to run all queries: 2.886 seconds

Minimum number of seconds to run all queries: 2.137 seconds

Maximum number of seconds to run all queries: 4.125 seconds

Number of clients running queries: 100

Average number of queries per client: 1
It’s helpful to use mysqlslap in this manner when you need to analyze the effect of a change in your database structure or indexing because it allows you to immediately grasp the impact of, say, an additional index on overall performance. To illustrate, look what happens to the time needed to run the previous query when an index is added to the City table:

mysql> CREATE INDEX idx_ccode ON City(CountryCode);

Query OK, 4079 rows affected (1.06 sec)

Records: 4079 Duplicates: 0 Warnings: 0 mysql> exit

Bye

shell> /usr/local/mysql/bin/mysqlslap –user=john –create-schema=world –query=”SELECT City.Name, City.District FROM City, Country WHERE City.CountryCode = Country.Code AND Country.Code = ‘IND’;” –concurrency=100 –iterations=5

Benchmark

Average number of seconds to run all queries: 1.682 seconds

Minimum number of seconds to run all queries: 1.396 seconds

Maximum number of seconds to run all queries: 2.109 seconds

Number of clients running queries: 100

Average number of queries per client: 1
You can tell mysqlslap to create a custom table for your load testing by using the –create command-line switch with a CREATE TABLE command.

Comparing table engines

A cool feature of mysqlslap is the ability to specify the table engine used in the test. This provides database designers with an easy way to compare the performance of different table types under different load conditions. The –engine switch accepts any of MySQL’s supported table types and creates test tables using the corresponding storage engine. Here’s an example of how it could be used:

shell> /usr/local/mysql/bin/mysqlslap –user=john –auto-generate-sql –concurrency=100 –number-of-queries=700 –engine=innodb

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 1.240 seconds

Minimum number of seconds to run all queries: 1.240 seconds

Maximum number of seconds to run all queries: 1.240 seconds

Number of clients running queries: 100

Average number of queries per client: 7 shell> /usr/local/mysql/bin/mysqlslap –user=john –auto-generate-sql –concurrency=100 –number-of-queries=700 –engine=myisam

Benchmark

Running for engine myisam

Average number of seconds to run all queries: 0.676 seconds

Minimum number of seconds to run all queries: 0.676 seconds

Maximum number of seconds to run all queries: 0.676 seconds

Number of clients running queries: 100

Average number of queries per client: 7

shell> /usr/local/mysql/bin/mysqlslap –user=john –auto-generate-sql –concurrency=100 –number-of-queries=700 –engine=memory

Benchmark

Running for engine memory

Average number of seconds to run all queries: 0.602 seconds

Minimum number of seconds to run all queries: 0.602 seconds

Maximum number of seconds to run all queries: 0.602 seconds

Number of clients running queries: 100

Average number of queries per client: 7
Saving reports

You might wish to save a mysqlslap report so you can compare it to a previous or future test run; you may also want to use the report as a reference when you’re configuring new systems.

The easiest way to save a mysqlslap report is to pipe the output of a mysqlslap run to a file, as below:

shell> /usr/local/mysql/bin/mysqlslap –user=john –auto-generate-sql –concurrency=100 –number-of-queries=1000 –number-char-cols=4 –number-int-cols=7 >> /tmp/output.log
You can force mysqlslap to generate reports in CSV format; this is often useful if you need to import the data into a spreadsheet or database to build graphical reports from it. To do this, add the –csv switch to your mysqlslap command line and specify the output filename as an argument to this switch. Here’s an example:

shell> /usr/local/mysql/bin/mysqlslap –csv=/tmp/output.csv –user=john –auto-generate-sql –concurrency=100 –number-of-queries=1000 –number-char-cols=4 –number-int-cols=7
Here’s what the CSV file would contain if you peeked inside it:

shell> cat /tmp/output.csv

,query,1.070,1.070,1.070,100,10
Trying mysqlslap

The mysqlslap utility makes it possible to benchmark and compare MySQL performance on different hardware, as well as accurately quantify the effect of a change in database design. Try mysqlslap out for yourself and see how well your database server behaves under pressure from thousands of client connections.

318 total views, 1 views today