MySQL

MySQL: install, manage, backup, update, customize

Work with MySQL – Basic WordPress Queries

2017/11/24
By
Modified: 2017/11/05

This article was born, as I was searching for ways to handle thousands of registered WordPress users, who never do anything, never return to the site, and never even change their default password.               Table of Topics -1030- List all registered users sorted by date -1060- Group registered users by email domain name -1090- Users who ever posted articles -1120- List of registered users who ever posted comments -1150- List of users, who posted messages in your bbPress Forum -1180- Group users by year and month of registration -1210- Count users in different roles -1240-  Users who never changed their default password -1270-  Users who changed their default password -1300-…

Read more »


How to Install WordPress 4.X on Windows 10 and IIS 7.5

2017/10/20
By
Modified: 2017/09/16
Pick-IIS-FastCGI-Option

This article is written for those who need to test/evaluate features of new WordPress release on a local machine before rolling it out to all the serves. This article describes running PHP on IIS 7.5 through FastCGI. For best results, reader is required to be able to start and stop services, understand registry manipulations, be comfortable with copy and paste inside a command prompt (Alt+Space, E, P). To achieve better portability, try to match your test machine directory structure as close as possible with your server. Other IIS Features to Pick If you are planning to test old ASP sites on the same machine, you also need to pick: - ASP…

Read more »

How to Convert Julian Date to Gregorian in MS SQL

2017/09/20
By
Modified: 2017/09/16
SQL - Convert JUL to GREG

Julian date is stored in YYYDDD format, for example 12/31/99 will be stored as 99365 and 01/01/2017 will be stored as 117001. If you need to convert the date from YYYDDD to any conventional date format like dd/mm/yyyy in SQL use steps below. The idea is to convert year and date portions separately and them add them together and use CONVERT function to get any date format that you require. Here is a script that shows the conversion process step-by-step. SELECT -- Limit output for 9 rows TOP 9 -- 00 Julian date - 91244 FAEFTB, -- 01 Greg year - 1991 cast(left(CAST(FAEFTB as decimal)+1900000, 4) as char(4)) AS GREGYEAR, --…

Read more »

How to Work with MySQL – Basic Tasks

2017/09/16
By
Modified: 2017/09/10
How to Work with MySQL – Basic Tasks

This material is tested with MySQL 5.1 and 5.5 on various Windows versions. This article covers these topics: -010- MySQL Data Location -020- Shortcut to MySQL Command Shell -030- Create a New Database -035- Delete an Existing Database -040- Display a List of All DB Users -050- Display a List of All Tables -060- List All Fields in a Table -070- Free ODBC Connector is Available -080- Create a New User -085- Change User Password -090- Delete a User -100- Grant User Rights to a D/B and Show It -110- Save Database into a Dump File -120- Restore Database From a Dump File -130- Rename Database -140- Backup Database -150- Automate…

Read more »

PHP chokes server with TEMP files – Updated and Solved

2017/08/11
By
Modified: 2017/07/22
FastCGI Settings

I am running PHP on IIS 7.5.   This is a Windows 2012 Server.  Fresh clean install every time. The only way to access and delete these TEMP files are by using  a command prompt.  And this deletion procedure will run for several DAYS . . .   The Problem The story goes like this.  Fresh new instance of a Windows 2012 Server is great.  It runs fast and smooth.  But after 5 or 6 months MySQL service having hard times to start up.  And a little later – couldn’t start at all.  The only way I can make it run is to start it from a command prompt.  And finally the whole server just slows…

Read more »

MySQL: Fantastic adventures – WordPress users and their roles

2017/08/05
By
Modified: 2017/06/25
MySQL: Fantastic adventures – WordPress users and their roles

This was exciting. After years of neglect, I have decided to examine, who is registering on my WordPress site, and what is that they are doing. I knew that most of them are just a result of robot activities, and they are just a dead weight in the database (useless users). However, what about the active users, who post in forums, and leave comments, and return regularly? Let me identify and cherish those. I knew I needed a monstrous SQL statement to link several files, and to count various record types, and sort all this in alphabetic order. So the plan was to learn the WordPress (WP) table structure, to start…

Read more »

Adding a new WordPress site to an Existing Installation

2017/07/12
By
Modified: 2017/06/25

Task You already have a working WordPress (WP) site.  Now, you need to add one more site with a new MySQL database.  This document describes how to add one more database and install a new WP site on the same server. Download Download latest WordPress ZIP package (3.X) and unzip it to your future site location. Open IIS Admin Create a new virtual directory pointing to that location Side Notes – Change root password You need to know existing root user password. Login into MySQL Command Line Client. Select database: use mysql; Reset user password: update user set password=PASSWORD(“NewPassword”) where User=’root'; Allpy new password: flush privileges;   Create a New Database…

Read more »

SQL DB2: Concatenate TEXT from multiple records

2017/06/30
By
Modified: 2017/06/25
Concatenation Road in Winter

    Scenario   Here is a recipe on how to concatenate a field from multiple records into one field. There are two pre-conditions for this method to work: First. Your source file needs to have an equivalent of a line number field.  This should be a numeric field indicating the sequence in which to concatenate the records.  This field doesn’t not need to be  consecutive. Second. The difference between lowest and highest line numbers should be a reasonably low number.  In this example the max difference is 11, and it means that at the very maximum we would need to concatenate 11 text fields together.   Step One Generate a driver file with MIN…

Read more »

MySQL Upgrade on Windows – Easy and Painlessly

2017/06/06
By
Modified: 2017/04/30

How to update MySQL Server on Windows Machine   Preliminary notes: These steps work for MySQL Server that is running as a service on Windows machine. These steps were tested, when MySQL data directory is located on a different drive from MySQL  installation directory.   Determine your current MySQL version.  Run MySQL command shell: ..\YourMySQLFolder\bin\mysql.exe -u root   You should see something like this: Server version: 5.5.8 MySQL Community Server (GPL)   Rename MySQL installation directory   Download a *.zip file from Oracle (not *.msi).   GA stands for “Generally Available”.   Select x32 or x64 package from: http://dev.mysql.com/downloads/mysql/   Stop MySQL Service   Create empty MySQL install directory and unzip content of…

Read more »

How to Move MySQL D/B Files (Windows)

2017/05/27
By
Modified: 2017/04/30

Locate your MySQL installation files and central configuration file basedir="C:/Program Files/MySQL/MySQL Server 5.1/" Your MySQL configuration is stored in "my.ini" file Locate your MySQL Database location: datadir="C:/Documents and Settings/All Users/ Application Data/MySQL/MySQL Server 5.1/Data/" Stop MySQL service. Modify My.ini file to point to a new D/B location and Save. Move your Data directory to a new location Restart MySQL service. You are done.  All pages (even if you were in the middle of the editing) continue to work.  Amazing!

Read more »

Move WordPress to a new Windows Server 2012 on Amazon AWS

2017/05/11
By
Modified: 2017/04/30
Move WordPress to a new Windows Server 2012 on Amazon AWS

This article is about moving your Windows-based WordPress and other sites (some ASP, some ASP.NET) from older to a newer instance using AMAZON cloud server  instances.  Procedures described here do not involve any installation.  Just copy files and edit CONFIG files.  We do not manipulate Registry as well (except to change inbound RDP port). Sample installation in this article was tested on Amazon Small instance.  Image used  was Windows 2012 Server x64 with SQL Server2012 Express.  This article can be used to create you own check list while preparing to migrate to a new Amazon server. Why would you decide to upgrade or move to a new instance/server?  Here are some driving reasons:  Something is no…

Read more »