MySQL administration - Learning | Reading articles

 

25 Point Basic MySQL Setup/Optimization Checklist

Here is the article. 

by jestep

Daily I run into new web programmers that are using PHP and MySQL to create their blogs and websites. I created this checklist as a guide for new and experienced to make sure they are covering the basics of a MySQL server setup.

This guide is by no means all inclusive, but should help to cover some of the major gaps in knowledge and commonly overlooked fundamentals that I run into on a daily basis.

The checklist is separated into 5 equal sections: Server Setup, Schema Design, Table Design, Index Optimization, Query Optimization, and a 6th Bonus Tips section.

You can also download a simplified summary on PDF form.

Section 1 – Server Setup

1. Root User
   For security reasons, the root MYSQL user must be setup with a secure password, and should only have access from localhost. It is a bad idea to allow outside access to the root account. Create additional users if you need to access the database remotely!
2. Backup and Restore
   Before allowing a database to be used in a production environment, there should be a usable backup and restore process. I use the phrase “in case the database server is completely destroyed” because the backup location and method needs to be completely independent from the database server. Note: Even a weekly database backup is better than no backup at all.
3. Benchmarking
   There’s no easy way to determine bottlenecks and trouble unless a method to benchmark performance is in place. The slow query log should always be enabled, and it’s a good idea to install a benchmarking program. Monyog is an external program that provides a number of real-time reports useful for monitoring and performance tuning.
4. DNS
   If you do not allow outside access, or you can access your server from known IP addresses, disabling DNS look-ups can speed up server operations. Additionally, if the MySQL server loses it’s DNS look-up service, the usability of the entire Database can all but halt.
5. Privileges
   When adding users to a database, only give them the permissions that are absolutely required, and be specific in where they can access from. “GRANT ALL ON EVERYTHING TO USER@ANYWHERE” is a really bad idea. If you do need to give users full permissions for installations or another purpose, it’s a good practice to change them back to the minimum once complete.

Section 2 – Schema Design

6. Naming
   A standardized naming scheme should always be used. The best practice is to use lowercase letters with an underscore connecting names, such as `my_personal_database`. Tables, and individual column names should carry the same naming convention. Use descriptive names for every column including id columns. `id` is not descriptive whereas `contact_id` is.
7. Collation
   Use the same collation for all parts of the database, and avoid using UTF-8 or multi-byte formats unless you specifically need them. Keeping the same format on all tables and columns can help prevent data corruption and conversion problems. UTF-8 requires significantly more disk space and overhead which can reduce performance. If you need UTF-8, use it , but don’t make your entire database UTF-8 because you’re lazy.
8. Foreign Keys
   Always use foreign keys to ensure that bad / incomplete data stays out of the database. Nothing replaces good application level programming, but foreign keys are the best way to prevent putting bad data into your database in the first place. You will need InnoDB to use foreign keys, but the benefit is worth it.
9. Logically Segmented Data in Tables
   Tables should be segmented logically by the data they contain and their association with other tables. In this manner, there may be more total tables, but will help eliminate tables with a huge number of columns which can really hurt performance. Additionally, it will make querying easier as it’s unlikely that every column is needed for every query. This also allows for Single-to-many relationships such as storing multiple addresses related to a single entry. Don’t be afraid of 20 tables with 20 columns each, be afraid of 1 table with 400 columns!
10. Reserved Words
    Avoid using reserved words for any name in your database schema. Words like date, time, decimal, etc. are often used, and can wreak havoc trying to get queries to work properly, and can cause even more difficulty in debugging. You can technically use these words if they are placed in back-ticks (`date`), but this is a bad practice and should always be avoided.

Section 3 – Table Design

11. Data types
    MySQL has many data types, probably more than any other database. Using the correct data type for the data being stored is one of the most important aspects in design. Failing at this step can break a database’s speed and the usability of an application.

    Whole Numbers – BIGINT, INT, MEDIUMINT, SMALLINT, TINYINT
    Decimals – DECIMAL, FLOAT, DOUBLE, REAL
    Dates – TIMESTAMP*, DATETIME, DATE, TIME, YEAR
    Strings – CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, SET

    Additionally, using the correct data type allows for the use of MySQL’s built-in functions which can sort, do math, comparisons, date conversions, etc. For example, I often see dates stored in VARCHAR columns, which completely prevents MySQL from sorting, or using any date related function.

12. Large Numerical Keys
    It’s common for new programmers to use a BIGINT(20) when they need a key column. While admirable, this is a waste of disk space. An UNSIGNED INT(10) has over 4 billion possible numbers, which is more than most will ever use. Even so, by that time, you will want to look into partitioning, and will have a variety of other problems on your hand. Don’t use BIGINT’s unless you need to store very-very large numbers.
13. Smallest Length
    Using the smallest length data length is important. Every byte of savings adds up when a database’s size and usage goes up. Lazy programming by using VARCHAR(255) or DECIMAL(20,2) creates unnecessary overhead and causes problems down the road. Give yourself one extra byte of space if needed, but 100 is a overkill.
14. Avoid TEXT and BLOB Columns
    TEXT and BLOB type columns can eat up a server’s resources when being selected. While these are most certainly needed to store larger amounts of data, they should only be used for that purpose. VARCHAR can hold up to 255 bytes and should always be used before TEXT whenever possible.
15. Non‐Relational Storage
    A huge design mistake is storing data in a non-relational format. It’s common to see data stored in a CSV format like (value1,value2,value3) in a single column. This effectively bypasses MySQL’s ability to use the data. It’s best to use multiple tables for single-to-many relationships, as this allows for MySQL to handle the data in an elegant manner. There are some situations where storing csv-like data would make sense, but for all intensive purposes, avoid storing data like this.

Section 4 – Index Optimization

16. Use proper indexes
    MySQL supports several types of indexes (PRIMARY, UNIQUE, NORMAL, PARTIAL, and FULLTEXT). It is important to use the correct type of index for the job. It is also important to only use indexes when needed, and not to create duplicate indexes. For example a primary key column already has an index, so adding a second UNIQUE INDEX on the primary key is a complete waste of overhead and disk space.
17. Multi-Column Indexes
    If there is a data set that is constantly queried with more than one column in the WHERE clause, it may be a good idea to create a multi-column index. If you have an index on (`user_id`,`user_category`) the index will work when both are in the where clause or the first column (`user_id`) is in the where clause. However, the index will not be used if only `user_category` is in the where clause.
18. Modifying Indexed Fields During a Query
    Unless you specify the length of an index, modifying an indexed column will prevent the index from being used.
    For example, if there is an index on `credit_card_number` and you perform a query like this:
    SELECT `user_id` FROM `my_table` WHERE LEFT(`credit_card_number`,4) = '5666';
    The index will not be used. If this was a common scenario, you could create a partial index of (`credit_card_number`,4), and the above query would use the index.
19. Indexes With a High Cardinality
    Indexes work best when there are many unique values in relation to the total number of rows. This allows the database engine to quickly reduce the number of possible rows in the result set. Indexes on columns with only a few unique values are inefficient and will end up being a waste of overhead.
20. Unique and NULL Column Indexes
    Allowing NULLS in index columns adds an additional byte of storage per row to the index. This again equates to a waste of space and overhead and will slow down MySQL’s performance. It’s better to use no value rather than NULL.

Section 5 – Query Optimization

21. Specific Column Names
    Always use specific column names instead of * when querying a table. SELECT * is lazy programming. While it is completely valid syntax, you won’t know the columns that will be returned. If you don’t know what you’re going to get with a query, there’s no reason to use it.. right? Write out any column names that you need data from. This way your code is intuitive, you won’t have problems trying to use data from a column that doesn’t exist, and the next person using your script wont hate you.
22. MySQL’s Built-in Functions
    MySQL has a variety of very advanced, and very fast, built-in functions. They probably are much more efficient than php or most other application level scripts. These functions can greatly increase your application’s speed, and reduce its complexity. MySQL has everything from mathematical operations, date comparisons, even spacial functions for calculation geographic equations. Learn to use them.
23. Selecting TEXT and BLOB Columns
    When a TEXT or BLOB column is select in a query, MySQL will create a temporary internal table. If large result sets are selected with TEXT or BLOB columns, this can create a major load on the database, and unnecessary overhead. This relates back to SELECT *, don’t select a TEXT or BLOB type column unless you actually need to use the data.
24. Use Transactions
    Transactions are another great way of preventing incomplete or corrupted data while inserting or altering data. When using a transaction, you can insert or alter any number of rows of data. If there is an error, all of the queries in the transaction will be aborted. Think of inserting 50,000 rows into a report table, and having 10 arbitrary rows not insert correctly. That entire set of data is now corrupt, and a transaction would have prevented that.
25. SQL_NO_CACHE
    SQL_NO_CACHE is a great way to prevent MySQL from caching a query’s result. This is important for results with a rapidly changing data, or very large result sets. Both of these situations can eat up server resources without any gain to the application or end-user.

Bonus

1. TIMESTAMP vs. DATETIME
   TIMESTAMP and DATETIME store dates in the exact same format (YYYY-MM-DD HH:MM:SS) but TIMESTAMP uses less space to do so. The only limitation is that TIMESTAMP cannot be used for dates older than Jan 1st, 1970.
2. SIGNED INT
   Unless you need to store negative numbers, only use UNSIGNED INT and other numerical data type fields. There’s no reason to allow for negative numbers if they will never be used.
3. Collation: _ci vs. _cs
   The _ci in a collation stands for “case insensitive”. If you care about case sensitivity use a collation that ends in _cs. The can be very important for searching and other operations where John ≠ john!
4. InnoDB vs. MyISAM
   If you’re using MyISAM as a storage engine only because it was the default, you may be making a mistake. InnoDB is superior in several areas (Reliability, Backups, Foreign Keys, and Performance in many situations) and while maybe not always the best option (Full Text Indexing), you should know why you’re using the engine you’re using. You can also mix the 2, but this can make performance tuning especially difficult.
5. Consult a Professional
   When you get a project and the database design, usage or other factor is just over your head, it’s a good idea to consult a professional. It may cost a fair sum, but the cost down the road could be substantial. Planning is always cheaper than reacting.

Security Checklist for PHP Web Applications

Here is the article. 

In Brief

The below is a short checklist outlining the most common vulnerabilities of web applications and applicable PHP solutions and best practices. Although focused on PHP, this checklist can be extended to all programming languages.

Validation of Inputs

Advice:

• during development the configuration error_reporting = E_ALL (without removing the E_NOTICE) is used to recognise uninitialised variables; in production, errors should not be displayed in the web interface;
• filter_var () and filter_input () statements are very useful for filtering and validating inputs;
• the use of content equality and === type avoids errors during comparison with Booleans. The expression (0 == false) is true while (0 === false) is false;
• casting can be faster than testing (compare the is_numeric function to a (int) $value type conversion);
• beware of the $_REQUEST global super variable because it may contain values from many different sources;
• check for different inputs using isset, for example isset ($ _ GET ['id']);
• also pay attention to some fields in $_SERVER such as $_SERVER ['HTTP_REFERER'];
• as we cannot trust browsers, pass $_FILES ['file'] ['name'] through the basename function when uploading files; (imagine $_FILES ['file'] ['name'] = '../../../etc/passwd')
• check the content of uploaded files. Images must be checked with the getimagesize() function, which returns false when the file is not of this type. Think also about the fileinfo extension;
• do not accept serialised objects as input.

In general, it is advisable to use the concept of a whitelist for many cases of validation.

An extra layer of protection or protection of an already existing application can be provided by adding “PHP Input Filter” and “PHPIDS” or “mod_security” if you use Apache

SQL Injection

Using raw SQL queries by concatenating variables to the query string is bad practice that can easily lead to unwanted SQL injection.

Advice:

• Use prepared statements. In PHP the easiest way to handle escaping variables is to use an abstraction layer such as PDO.
• Parameterised queries are also accessible for some databases through basic PHP functions (e.g. postgresql or pg_prepare) or with extended libraries such as mysqli.
• If concatenation cannot be avoided, it is imperative to escape the variables using functions of the type “(real_) escape_string”. For mysql, for example, find out about the mysql_real_escape_string function. It is useful to add that the PHP manual advises against using this function, but it can be useful for quick securing of an old application.
• Avoid at all costs automatic techniques such as magic_quotes and/or generic add_slashes. Each database will react differently to these escape methods.

Cross-Site Scripting (XSS)

Consists of the use of javascript as input to be executed during display. This is a very common and dangerous vulnerability, although it is easy to avoid in many cases. You will find a fairly complete list of possible attacks on the OWASP website.

• transform all characters of displayed variables into html entities. In PHP it is therefore often enough to pass all the variables displayed by the htmlentities function. Some “templating systems” such as “flexy” make an automatic escape;
• if you need to pass HTML in the variables, the strip_tags function can be used to filter permitted tags. However, this function is dangerous because of the javascript injection in the permitted arguments; e.g.: <img onmouseover="alert(‘XSS’);" src="" /> or <a href="javascript:alert(‘XSS’);">link</a>. It is, therefore, usually necessary to create your own filter function in addition to strip_tags;
• a good way to filter html is the “html purifier” class (http://htmlpurifier.org/, LGPL license).

In general, a generic display function must be written and used during any display.

Code Injection

Injection of malicious code that will be executed by the application.

• avoid using variables in the include or require instructions. If it cannot be avoided, the use of whitelist filters is important;
• avoid the “eval” instruction at all costs or treat it with the utmost care; (alternatives (http://www.php.net/manual/de/language.variables.variable.php), closures (http://www.php.net/manual/de/functions.anonymous.php) and the call_user_func()) function
• in php.ini, set allow_url_fopen to off if the function is not required.
• avoid variables in preg_replace type functions.

Injection of Instructions

Some functions can be used to run system instructions.

• avoid variables in instructions such as shell_exec, exec, system, passthru, popen;
• if they cannot be avoided, the escapeshellarg() and escapeshellcmd() functions must be used;
• use whitelists and the basename function in filenames.

Session Security

By stealing the session identifier, other users’ sessions can be used. See “firesheep”, for example.

• avoid using the session identifier in the URL because another site could steal the identifier by checking the “referer” or might even set your session identifier (imagine a link to a site such as link?PHPSESSID=123). So set use_only_cookies to 1 or On in php.ini;
• only use SSL (TLS) communications, otherwise the session cookie may be intercepted on hostile networks. Require the session cookie to be communicated only in SSL: session.cookie_secure=1;
• set an expiration time for sessions
• associate visitor IP addresses with their sessions.

Cross-Site Request Forgery (XSRF)

Involves the use of the open session in another browser tab by a malicious web page. Most often the vulnerable parts will be forms, which could be completed by a malicious site. In this case it is easy to avoid this type of attack by creating a unique identifier for the form and checking it during form submission. It is good practice to use a nonce created by a function such as uniqid or pseudo random hash with a secret code by using the hash_hmac function, which is placed in a hidden field on the form and in session, to be checked during submission.

To protect non-form functionalities, case-by-case analysis is required.

Best Practices for Storing User Passwords in the Database

Passwords must be stored in the database in hashed form. To do this, it is important, above all, not to use md5 or sha1 type algorithms, for which there are “rainbow tables”, which can be used to obtain the password quickly from the hash.

In addition, specifically to avoid the use of rainbow tables, and to hide identical hashes from the same password, it is important to use a salt. This salt will be determined randomly and concatenated to the password before hashing. The salt and the hash will therefore be stored in the database.

It is also important to add a secret salt constant that would be stored somewhere other than in the database, for example in a configuration file.

The use of a concatenated salt can be usefully replaced by a hash_hmac hash function, but we recommend the use of the crypt function (http://php.net/manual/en/function.crypt.php) which enables a cost factor to be added to the calculation when using CRYPT_BLOWFISH, CRYPT_SHA256 or CRYPT_SHA512 (see key stretching).

The hash calculation will then have the following form, for example:

$hash=crypt(hash_hmac('sha512','secret password',$saltsecretconfig), '$2y$’.$cost.'$randomsaltDB$') ;

The variable $cost will need to be set to a number such that the duration of the operation is viable for your application, but prohibitive for a brute-force attack (1/100 seconds for example).

PHP 5.5 introduces new hash functions that enable secure storage of passwords (see http://www.php.net/manual/en/function.password-hash.php).

The function above becomes:

$hash = password_hash(hash_hmac(‘sha512’, ‘secret password’, $saltsecretconfig), PASSWORD_BCRYPT, ['cost'=>$cost]); // from PHP 5.5

Use of Countermeasures

In general, a web application will be scanned with an automatic tool by an attacker before compromise. It is therefore possible to place cyber traps or other “tar pits” on the application. A practical example called “weblabyrinth” can be downloaded from http://www.mayhemiclabs.com/content/new-tool-weblabyrinth. But useless scripts containing “sleep” functions or other false authentications can already perform this function.

Webroot! = Approot

Placing libraries and other scripts in places that cannot be accessed directly from the Internet is a definite advantage. It means that libraries with vulnerabilities cannot be exploited directly.

Denial of Service

It is not possible to defend entirely against denial of service type attacks. However, it is possible to optimise applications so that they respond better. Although this is beyond the scope of this document, the following are some concepts that can be explored:

• use “foreach” only for associative arrays and generally avoid any unbounded iteration on data from databases;
• use a compiled code cache engine (memcache, eaccelerator, Zend Platform, …);
• some debugging tools can report bottlenecks to you (xdebug, yslow, firebug, …)
• compress your javascript;
• check for slow queries (mysql_slow_query);
• possibly compress server-client communication (only for heavy traffic and where the server CPU is sufficiently powerful);
• if you use Apache consider using the mod_evasion module.

Frameworks

Many frameworks are written by experienced developers and benefit from their extensive security experience and good programming practices. They often, therefore, include many of the techniques outlined above and enable web applications to be developed more quickly and securely. Examples: Zend, Symfony…

Advice for php.ini

The open_basedir configuration function is used to define the directories to which the application has access and therefore offers higher security.

The following php.ini parameters should be set to the needs of your application while trying to keep them as low as possible. In general it is advantageous to set these parameters on the fly with ini_set in scripts that need more resources than the bulk of the application:

• max_execution_time
• memory_limit
• post_max_size
• upload_max_filesize

Check this value in the production environment:

• display_errors = Off

Wordpress website check list for administrator

 Here is the article.

WordPress Website Launch Checklist: 30+ Must-Do Things For Success In 2022

Whether you are running an online business, an eCommerce store, or you simply want to create your own personal blog or portfolio, you should consider building a website on WordPress if you haven’t already. But where and how should you start? The best way to go about it is by using a comprehensive WordPress website launch checklist, like the one we are sharing in today’s blog post. 

Whether you are running an online business, an eCommerce store, or you simply want to create your own personal blog or portfolio, you should consider building a website on WordPress if you haven’t already. But where and how should you start? The best way to go about it is by using a comprehensive WordPress website launch checklist, like the one we are sharing in today’s blog post. 

Why Should You Get A Detailed Website Launch Checklist?

It’s hard to keep track of all of your ideas and tasks in our heads. We are human after all, and it’s much easier to get things done the right way when we have a checklist or a to-do list. Starting your WordPress website doesn’t have to be difficult, but you can definitely save yourself the headache with a detailed website launch checklist. 

To help you out, we are sharing our own website launch checklist for free, so you can focus your time and energy on getting things done without making any mistakes. Just follow the steps given below, and you will be able to confidently launch your website.

Step 1: Getting Started With Your WordPress Website

As with every good thing, getting started with your WordPress website will require a bit of preparation at first. For starters, you’ll need to decide the purpose of your website. Once that’s done, you should pick a catchy, unique, memorable name for your website that properly reflects what your website is about.

This is one of the most important parts when it comes to launching your website. It’s crucial that your website name is unique and memorable because you will need to find an available domain name for your site. Let’s learn a little more about domain names and why you should invest in one below.

Register A Domain Name For Your Website

Just like your home has a street address to help people find out where you live, your domain name is the address of your website that people can use to find your site on the Internet. This domain name is unique for every website, and getting one can help you properly establish your site identity and online presence. 

In addition to this, purchasing your domain name will give you more credibility and exclusive ownership of your website. So, the first thing on your website launch checklist should be getting a domain name for your WordPress site.

Choose A Managed Hosting Provider

After registering your domain name, you should choose a managed hosting provider. With a managed hosting provider, you will not have to worry about the day-to-day management of your WordPress website. Your hosting provider will take care of these issues for you, and configure your website settings to ensure the best security and performance. 

Wondering which managed hosting provider you should go for? There are tons of great options out there. Check out our list of the best-managed WordPress hosting providers here to choose the right hosting provider for your website. 

Step 2: Configure Your Website Basic Setups

So, now that you have your own domain and hosting provider, it’s time to get to work. In this step, you need to configure the basic settings of your website to get it up and running properly. Here are the things you’ll need to do.

Set Correct Timezone & Date Format For Your WordPress Website

First, you need to set the correct timezone for your website. This sounds like a very simple step, but it is definitely an important task to add to your website launch checklist. Setting the right time zone will help you schedule your content at the right time.

To do this, you have to navigate to Settings→ General from your WordPress dashboard. There, at the very top of the page, you will see the option to change your timezone. By default, it is set to UTC +0, but you can change it to your preference using the dropdown menu. 

Change Your Website Admin Email Address

Next, you should configure your website admin email address. Usually, those who have just started using WordPress enter their personal email address when setting up their website, or they use the email address provided by their hosting providers. Since the admin email address is used by WordPress to send important notices, as well as for password recovery and changes, it’s important that you change your website admin email address as soon as you can.

You can do this easily by navigating to Settings→ General and scrolling down to the Administration Email Address option. Here you can add your preferred email address to which WordPress will send important notices.

SmartCrawl WordPress SEO checker, SEO analyzer, SEO optimizer

 

Description

Give your site better SEO optimization & ranking with SmartCrawl. Improve keyword optimization, XML sitemaps, optimize your meta tags, titles and descriptions and boost your PageRank on Google.

If you’re looking for the best SEO plugin for WordPress, you need to give SmartCrawl a try. With SmartCrawl, there’s no more juggling settings, making guesses, and wondering if your SEO is properly optimized. Use SmartCrawl’s one-click setup, automatic XML sitemaps, improved social sharing, real-time keyword and content analysis, and scans and reports.

Create clear, bold, targeted content and rank at the top of your favorite search engine – from Google to Bing.

SMARTCRAWL’S SEO TOOLS FOR WORDPRESS INCLUDE:

• One-Click Setup Wizard – Activate settings to boost your reach – no more guesswork!
• SEO Checkup & Reports – Run a Checkup and get recommendations for improving SEO.
• Titles & Meta Descriptions – Customizes how your meta titles and meta descriptions display on search pages.
• Leverage Social Media – SmartCrawl includes Open Graph, Twitter card, and Pinterest verification and credits you when someone shares your posts.
• Sitemap Generator – Choose which post types, archives and taxonomies you wish to include, exclude or add to the XML sitemap.
• Smart Page Analyzer – SmartCrawl has an SEO checker that scans pages and posts for readability and keyword density and makes suggestions for optimizing your content.
• SEO Crawl – Every time you add new content to your site, SmartCrawl will let Google know it’s time to re-crawl your site.
• Schema Markup Support – Make it easier for search engines to understand the meaning of your content.
• Schema Types Builder – Add and customize a range of schema markup types.
• 301 Redirect – Use SmartCrawl to redirect traffic from one URL to another to protect your hard work and take advantage of high producing links.
• Integrate With Moz SEO Tools – Already using Moz? Connect your Moz reports and comparison analysis, including rank and links.
• Quick Setup Import/Export – Quickly add your custom SmartCrawl SEO settings to all your sites with included import.