You have this issue because your server run an older versions of MySQL.

Open your sql script to import with a text editor and replace the collation from utf8mb4/utf8mb4_unicode_ci to utf8/utf8_general_ci

If you use linux, you can do it via shell

sed -i 's/utf8mb4/utf8/g' your_file.sql
sed -i 's/utf8_unicode_ci/utf8_general_ci/g' your_file.sql
sed -i 's/utf8_unicode_520_ci/utf8_general_ci/g' your_file.sql

and before load the script, create the database with the  command


CREATE DATABASE mydb CHARACTER SET utf8 COLLATE utf8_general_ci;

If your sql script is for Wordpress,  remember to change in wp-config.php:

define('DB_CHARSET', 'utf8mb4');

to

define('DB_CHARSET', 'utf8');

and also don't forget to modify DB_COLLATE with

define('DB_COLLATE', 'utf8_general_ci'); 

This morning one of my sites using Wordpress and MySQL stopped working indicating an error in the connection to the database. Trying to connect with the client mysqlyog I obtained the following error message.

ERROR 1820 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.

The problem is because mysql 5.7.4 or later will expire all user password after 360 days by default!!

How to solve this problem? Login by command line client and setup a new password

C:\wamp\bin\mysql\mysql5.7.9\bin>mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.9

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET PASSWORD = PASSWORD('newpassword');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'default_password_lifetime';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| default_password_lifetime | 360   |
+---------------------------+-------+
1 row in set (0.00 sec)

You have to modify the value of this variable in order to avoid the problem again after 360 days.

mysql> set global default_password_lifetime=0;

Query OK, 0 rows affected (0.00 sec)

1. Create your table:

   mysql> create table foo(id INT, mytext TEXT, sparkle DECIMAL(9,4));
   Query OK, 0 rows affected (0.02 sec)

2. Create your data file, put this in foo.txt, note tabs delimit the 3 columns

   1   twilight sparkle is best pony!  6.6 
   2   pinkie pie is best pony!    3.3 
   3   derpy hooves is best pony!  1.1 

3. Then import the file:

   mysqlimport --fields-terminated-by='\t' 
               --columns=id,mytext,sparkle 
               --local -u root -ppassword your_database foo.txt
   yourdatabase.foo: Records: 4  Deleted: 0  Skipped: 0  Warnings: 3

4. Look in the table.

   mysql> select * from foo;
   +------+-------------------------------+---------+
   | id   | mytext                        | sparkle |
   +------+-------------------------------+---------+
   |    1 | twilight sparkle is best pony |  6.6000 |
   |    2 | pinkie pie is best pony       |  3.3000 |
   |    3 | derpy hooves is best pony     |  1.1000 |
   +------+-------------------------------+---------+
   4 rows in set (0.00 sec)

The rows were loaded.

The Apache HTTP Server Project itself does not provide binary releases of software, only source code. You can obtain a binary package from third-party web sites:

• ApacheHaus
• Apache Lounge

Download either the 32-bit version (httpd-2.4.20-win32-VC14.zip) or 64-bit version (httpd-2.4.20-win64-VC14.zip). Check if your Windows is 64-bit, you should always try to install 64-bit version of software. To find the OS version, you can use the msinfo32.exe. You can use this tool to gather information about your computer, to diagnose issues with your computer, or to access other tools.

To start this one, Run > type msinfo32 > hit Enter.

After you have find the right version, download the Zip file and then extract the file to a directory called C:\Apache24

Before start the Apache server, if you want change the listen port to 8181

Open C:\Apache24\conf\httpd.conf file in your text editor. Find this line:

Listen 80 

and change it

Listen 8181

After the change, save it and you are ready to start the apache web server.

Open the Command Prompt as Administrator and change to the bin sub-directory

C:\Apache24\bin

Type httpd.exe and press Enter.

If you get an error dialog stating that MSVCR140.dll is missing, you’ll need to install the Visual C++ Redistributable for Visual Studio 2015. (Select vc_redist.x64.exe if your Windows is 64-bit)
https://www.microsoft.com/en-us/download/details.aspx?id=48145

Now open the browser and type http://localhost:8181 to see the demo web site up and running.

 Install PHP 7 on Windows

PHP 7, a major update to the server-side web development language PHP. Download the VC14 x64 Thread Safe (2016-Apr-29 00:38:19) zip file (if you downloaded the 32 bit version of Apache then download PHP x86, don't mix versions).

http://windows.php.net/download/

Create a folder named "php7" then extract the file php-7.0.6-Win32-VC14-x64.zip into the folder and move it to C:\

Configure Apache to use PHP

Open the Apache Configuration file C:\Apache24\conf\httpd.conf
Copy the following lines on the top of the file:

• AddHandler application/x-httpd-php .php 
• AddType application/x-httpd-php .php .html 
• LoadModule php7_module "c:/php7/php7apache2_4.dll" 
• PHPIniDir "c:/php7" 

In the section <IfModule dir_module> add the line index.php and place it before index.html

<IfModule dir_module>
    DirectoryIndex index.php index.html
</IfModule> 

Save all. Now rename the file C:\php7\php.ini-development to C:\php7\php.ini

Enabling mod_rewrite for URL rewriting

Open the Apache Configuration file C:\Apache24\conf\httpd.conf
Find the line #LoadModule rewrite_module modules/mod_rewrite.so and remove the hash ‘#’
Find any additional occurrences of the line "AllowOverride None" and change it to "AllowOverride All"

Configuring PHP with MySQL

Edit the php.ini file and uncomment the extension directory. Remove the ";" to uncomment:

; Directory in which the loadable extensions (modules) reside.
; http://php.net/extension-dir
; extension_dir = "./"
; On windows:
extension_dir = "ext"

Uncomment the following lines, this will enable the starting of the MySQL modules

extension=php_mysqli.dll
extension=php_pdo_mysql.dll

and these other lines if you use the installation in a development environment

extension=php_curl.dll
extension=php_fileinfo.dll
extension=php_gd2.dll
extension=php_mbstring.dll
extension=php_openssl.dll
 

Uncomment the error_log to enable file logging

; Log errors to specified file. PHP's default behavior is to leave this value
; empty.
; http://php.net/error-log
; Example:
error_log = c:\php7\php_errors.log

Save the file changes.

IMPORTANT! Set your PATH for Windows to find PHP

• Right-click on "My Computer"
• Properties 
• Advanced Tab > Environment Variables 
• Under System variables, scroll down to find "Path", select and click on Edit.
• Add path to your php install on the end (make sure to precede with semi-colon ";"). Example: ";C:\php7"
•  Click Okay.

How to check if php is installed

Create the following text file C:\Apache24\htdocs\phpinfo.php

Restart Apache and then go to your browser and write http://localhost:8181/phpinfo.php this page should show the details of your PHP installation.

Register Apache Service

If you want to register Apache as a Windows Service, open a command prompt and type:

C:\Apache24\bin\httpd -k install 

If do not want Apache starting automatically at start-up/reboot type:

C:\> sc config Apache2.4 start= demand 

Sometimes you want to keep a backup of all MySQL databases, this can be a problem if you have more than 100 MySQL databases! There is an easy way to export all of them at the same time and again import the all of them into MySQL server at one time. You must use Mysqldump command.

Export:

mysqldump -u root -p --all-databases > alldb.sql

Import:

mysql -u root -p < alldb.sql