Knowledgebase

Viewing articles tagged 'databasesphpmyadmin'

A Database Checklist Everything that matters.SETUPOne database per application One user per database, with all... A Practical Database Task Reference Common jobs and where they are covered.TAKING A BACKUPcPanel > Backup > Partial Backups, or... Altering Table Structure Adding, changing and removing columns.WHEN YOU WOULDAdding a column for custom data Widening a... Archiving Old Data Reducing a database without losing records.WHYA shop with years of orders, or a site with years... Auditing Content with Queries Understanding what your site actually contains.USEFUL AUDITSHow many published posts, pages and... Auto-Increment Problems When new rows fail or get wrong ids.WHAT AUTO-INCREMENT ISThe counter assigning the next id when... Backing Up a Database Properly The most important backup you take.WHY IT MATTERS MOSTFiles can usually be reinstalled. The... Browsing and Searching Data Finding what you need without SQL.BROWSINGSelect a table and click Browse. Rows appear in... Cleaning Up Database Bloat Reclaiming size and speed.WHERE BLOAT ACCUMULATESPost revisions, one per edit, indefinitely... Cleaning Up WordPress Revisions and Transients The two largest sources of bloat.REVISIONSWordPress stores a copy of a post every time it is... Cloning a Database for Staging Making a copy to work on.WHYTesting updates, changes or development against real data without... Common Database Mistakes What goes wrong.RUNNING UPDATE OR DELETE WITHOUT A WHERE CLAUSEChanges or removes every row. No... Comparing Two Databases Finding what differs.WHEN YOU NEED ITConfirming a migration copied everything Comparing staging... Counting, Summing and Grouping Aggregate queries.COUNTINGSELECT COUNT(*) FROM table;With a condition:SELECT COUNT(*) FROM... Creating a Database in cPanel The standard procedure.THE STEPScPanel > MySQL Databases.Under Create New Database, enter a... Creating a Table When you need somewhere to put data.WHENA custom feature storing its own data An import needing a... Creating and Managing Database Users Accounts that access databases.WHAT A USER ISAn account permitted to connect to one or more... Database Backups Versus Full Backups Knowing which you need.A DATABASE BACKUPThe content, settings, users and orders. Small, fast, and... Database Considerations When Changing Hosts What to plan for.BEFORE MOVINGNote the database version at both ends Note the character set and... Database Corruption and How to Handle It Tables in an inconsistent state.THE SYMPTOMSTables marked as crashed Errors when reading specific... Database Errors After an Update When an update leaves things inconsistent.WHAT HAPPENSMany updates change the database structure... Database Limits on Your Plan What is allowed.WHAT IS TYPICALLY LIMITEDThe number of databases Total disk space, which... Database Maintenance Routine What to do and how often.MONTHLYCheck the database size in cPanel > MySQL Databases Clear... Database Naming and Organisation Conventions that help later.NAMING DATABASESDescriptive of the site or application: shop, blog,... Database Terminology Explained The words you will encounter.TABLE: a collection of rows of one kindROW or RECORD: one itemCOLUMN... Database Was Modified by an Attacker Recovering from a compromise affecting data.WHAT IT LOOKS LIKESpam links injected into post... Databases: Everything That Matters, Briefly The whole category in one page.WHAT A DATABASE ISThe half of your site holding content, settings,... Desktop Database Tools Working from your own machine.WHAT THEY AREApplications that connect to a database and provide a... Diagnosing Database Connection Problems Working through the causes.THE ERRORError establishing a database connection, or access denied,... Drupal Database Notes Working with a Drupal installation.THE CONFIGURATIONsettings.php holds the database credentials,... Editing Data Directly Changing a value by hand.WHEN THIS IS APPROPRIATECorrecting a site URL after a migration Fixing a... Exporting a Database Taking a backup.THE PROCEDURESelect the database in the left panel.Export tab.Quick export, SQL... Exporting Data to CSV or Spreadsheet Getting data out in a usable form.FROM A QUERYRun a SELECT, then use the export option beneath... Exporting Only Part of a Database Taking specific tables or rows.WHYRecovering one table from a backup Moving specific data between... Extracting Data for Reporting Getting information out.WHYProducing a customer list Analysing orders Auditing content Providing... Finding Duplicate Rows Identifying repeated data.WHY IT ARISESAn import run twice A form submitting twice A plugin... Finding Slow Queries Identifying what is actually slow.DURING A PAGE LOADA query monitoring plugin lists every query... Finding Which Database Belongs to Which Site Untangling a multi-site account.THE RELIABLE METHODRead each site's configuration file.WordPress:... Fixing the WordPress Site URL in the Database When you cannot reach the admin.WHEN YOU NEED THISAfter a migration, where the site redirects to... Getting Help with Database Problems What we can do.WHAT WE CAN HELP WITHCreating databases and users Correcting ownership and... Getting Started with phpMyAdmin The interface explained.OPENING ITcPanel > phpMyAdmin. You are logged in automatically with... Handling a Database That Will Not Import When the dump refuses to load.THE COMMON CAUSESThe file exceeds the upload limit The import times... Handling Character Set and Encoding Problems Mangled characters after a move.THE SYMPTOMAccented characters, curly quotes and symbols... Handling Too Many Connections Connection limits reached.WHAT IT MEANSThe limit on simultaneous connections was reached, and new... How Much Database Does Your Site Need Size, growth and limits.TYPICAL SIZESA small brochure site: a few megabytesA blog with several... How Your Website Connects to Its Database The configuration that makes it work.THE FOUR VALUESDatabase name Username Password HostAll four... Importing a Database Restoring or migrating data.BEFORE IMPORTINGDrop the existing tables. Importing over existing... Importing CSV Data Loading data from a spreadsheet.WHENBulk-loading products, customers or reference data Migrating... Joining Tables in Queries Combining related data.WHYUseful information usually spans tables. Orders in one, customer... Joomla Database Notes Working with a Joomla installation.THE CONFIGURATIONconfiguration.php holds the database name,... Laravel and Framework Database Notes Application databases you or a developer built.MIGRATIONSLaravel and similar frameworks define... Managing Autoloaded Data in WordPress A specific and common performance problem.WHAT IT ISOptions marked for autoload are loaded into... Merging Data Between Two Databases Combining content from two sites.WHEN THIS ARISESConsolidating two sites into one Recovering... Migrating a Database to Another Server Moving between hosts or accounts.THE PROCEDUREExport from the source, using the command line for... Monitoring Database Growth Over Time Spotting problems before they matter.WHAT TO RECORDTotal database size, monthly The size of the... Monitoring Database Health Noticing problems early.WHAT TO WATCHTotal size, and its growth rate The largest tables Query... Moving a Site Between Domains Updating the database for a new address.THE STEPSBack up the database.Update the site URL... MySQL and MariaDB Explained The two names you will see.WHAT THEY AREDatabase server software. Your application connects to it... NULL and Empty Values A distinction that causes bugs.THE DIFFERENCENULL means no value exists. An empty string means a... Object Caching and Its Effect on Databases Reducing repeated queries.WHAT IT ISStoring the results of database queries in memory, so... phpMyAdmin Settings Worth Changing Making the tool more usable.WHEREThe Settings link on the phpMyAdmin home page.WORTH ADJUSTINGThe... Preventing SQL Injection The most serious database vulnerability.WHAT IT ISAn attacker supplying input that becomes part... Reading an EXPLAIN Result Understanding how a query will run.WHAT IT DOESPrefix any SELECT with EXPLAIN and the database... Recovering a Deleted Database When it is gone.FIRSTStop. Do not create a new database with the same name, which complicates... Recovering a Single Deleted Row Getting one record back.THE SITUATIONOne post, one order, one customer deleted in error. The rest... Reducing the Number of Queries Per Page Fewer requests to the database.WHY IT MATTERSEach query has overhead. A page running four hundred... Remote Database Access Connecting from outside the server.WHAT IT ISPermitting a connection to your database from an... Renaming or Removing a Database Changing what exists.RENAMINGcPanel offers a rename function under MySQL Databases.It works, and... Repairing and Optimising Tables Maintenance operations.REPAIRFor a table marked as crashed, usually after an interrupted write or... Resetting a WordPress Password in the Database Regaining access when locked out.WHEN YOU NEED ITThe password reset email does not arrive,... Restoring a Database from Backup Getting data back.BEFORE RESTORINGTake a copy of the current state. It contains everything added... Running SQL Queries Safely Using the SQL tab without breaking things.THE DISCIPLINEWrite a SELECT first, with the same WHERE... Scheduled Database Maintenance with Cron Automating cleanup.WHAT TO AUTOMATEClearing expired transients Removing old revisions Deleting... Search and Replace in a Database Changing values across many rows.WHEN YOU NEED ITAfter a domain change After moving from http to... Securing Your Database Practical protection.THE ESSENTIALSA strong generated password, different for every database user... Splitting a Large Database Export When the file is too big to handle.WHEN THIS ARISESA database too large for phpMyAdmin's upload... Storage Engines: InnoDB and MyISAM The two you will encounter.WHAT THEY AREThe mechanism a table uses to store and retrieve data.... Table Locking and Concurrency Why writes sometimes wait.WHAT LOCKING ISWhile one operation writes, others may have to wait, to... Testing Queries Safely Working out what a query does before it does it.THE METHODSRun it as a SELECT first, with the... The WordPress Database Structure What each table holds.THE CORE TABLESwp_posts: posts, pages, products, revisions, attachments,... Timezones and Date Storage Why times appear wrong.THE COMMON CONFUSIONTimes displaying offset by hours from what you... Understanding and Adding Indexes The most effective database fix.WHAT AN INDEX ISA structure allowing the database to find... Understanding Collation Mismatches Why some joins fail or behave oddly.WHAT COLLATION ISThe rules for comparing and sorting text:... Understanding Column Data Types What each column can hold.NUMBERSINT for whole numbers, with variants for different ranges... Understanding Database Privileges What a user is permitted to do.THE COMMON ONESSELECT: read dataINSERT: add rowsUPDATE: change... Understanding Serialised Data Why some values must not be edited by hand.WHAT IT ISA way of storing a structure, such as a list... Understanding SQL Query Basics Enough to be useful.SELECTReads data.SELECT * FROM table_name LIMIT 10;The asterisk means every... Understanding Tables, Rows and Columns The structure of stored data.A TABLEHolds one kind of thing. A posts table holds posts; a users... Undoing a Bad Query When you have just broken something.STOP IMMEDIATELYDo not run anything else. Further changes... Useful Queries for WordPress Sites Common tasks.BEFORE ANY OF THESEExport the affected table.CHECKING THE SITE URLSELECT * FROM... Using the Command Line for Large Databases When phpMyAdmin is not enough.WHYphpMyAdmin runs inside a web request and is subject to time and... Using Transactions Grouping changes so they apply together.WHAT IT ISA set of changes treated as one unit. Either... Using WP-CLI for Database Work The command line tool for WordPress.WHAT IT ISA command-line interface to WordPress, available... What a Database Actually Is The part of your site that holds everything.THE SHORT VERSIONYour website has two halves. Files... When You Actually Need to Touch the Database And when you do not.YOU DO NOT NEED TOEdit content, which the application does properly Change... When Your Database Has Outgrown Shared Hosting Recognising the limit.THE SIGNSQueries consistently slow despite indexes and caching Connection... Where Your Database Lives Finding it in cPanel.LISTING YOUR DATABASEScPanel > MySQL Databases. It shows every database... Why Databases Get Slow Understanding the causes.THE MAIN CAUSESMissing indexes on tables that have grownBloat:... WooCommerce Database Considerations What changes when you run a shop.THE ADDITIONAL TABLESWooCommerce adds its own tables for... Working with Databases for Other cPanel Applications Not every database belongs to a website.WHAT ELSE USES DATABASESRoundcube webmail, which stores...
Back

Are you happy with your experience? Leave us a review on Trustpilot.


Trustpilot