You can backup a table in a MySQL or MariaDB database by using the mysqldump backup tool included with both database management systems. To backup a specific table you can use the command mysqldump database_name table_name where database_name is the name of the database that contains the table and table_name is the name of the particular table you wish to backup. E.g.:

mysqldump -u ptolemy -p Planets Mars > Mars.sql

In the above case, if I have a database named Planets with a table within it named Mars, I could backup just that one table from the database with the command above. The -u option to the command allows you to specify a MySQL/MariaDB username that has access to the database and the -p option will prompt you for the password associated with that username. The Mars.sql file will then contain all of the Structured Query Language (SQL) commands needed to recreate the table structure and the data in the table. E.g., it will contain insert SQL commands that will insert entries in the table.

[ More Info ]

[/software/database/mysql] permanent link

SELECT * FROM Books WHERE Title LIKE "%Unix%";

The percent sign (%) serves as a wildcard character indicating that any number of other charcters will match it. So by putting the percent sign before and after the word Unix, the expression will find any books with titles that have "Unix" anywhere in the title, e.g., at the beginning, middle, or end of the title. If I only wanted to find those records in the Books table where the title began with the word Unix, I could use the command below:

SELECT * FROM Books WHERE Title LIKE "Unix%";

Any characters that came after the word Unix would match, but the title would need to start with "Unix". To find only those books where the word appears at the end of the title, I could use the following command:

SELECT * FROM Books WHERE Title LIKE "%Unix";

If I wanted, instead, to find all of the records where the title doesn't contain the word "Unix", I could put NOT before LIKE.

SELECT * FROM Books WHERE Title NOT LIKE "%Unix%";

[/software/database/mysql] permanent link

E.g., suppose I have a table named "Students" in a database I'm currently using with a column in the table named "LastName". Suppose, there are students named Smith whose last name has changed to Lamb and I want to change all instances where an entry in the table has "Smith" in the LastName field to "Lamb". I could use the following SQL command:

If you want to change every entry in a table, simply leave off the WHERE clause.

If you want to change multiple fields/columns in a table at the same time, you can separate them with commas in the SET statement, i.e., SET column1=value1,column2=value2,.... E.g., suppose all of the students with a last name of Smith are also undergoing an address change as well as a change to their last name.

[/software/database/mysql] permanent link

$ mysql --user=users_acct --password=users_password

If you viewed the contents of the Bash history file for that user's account you would see the command with the password just as you would see other commands entered from the user's account. E.g., if the user's account was jdoe:

# grep mysql ~jdoe/.bash_history
mysql --user=users_account --password=users_password

But, if the user entered the command mysql -u users_acct -p and didn't follow the `-p` or `--password` with the password, but, instead, just entered one of those parameters without putting the password immediately after it, that leads to the system prompting the user for the password and the password won't be in the Bash history file.

Note: if the user is still logged in to the account for which you are checking the .bash_history file, you won't see the commands entered during that login session until after the user logs out of the session.

If you are logged into the relevant account or use the su command, which is also referred to as the "substitute user", "switch user", or "super user" command, you can use the history command to view the commands entered at the command line. E.g., you could use history | grep mysql.

[/software/database/mysql] permanent link

MariaDB [(none)]> select user from mysql.db where db='Abc';
Empty set (0.00 sec)
MariaDB [(none)]>

In the above example, no accounts have been granted access to the database. If I want to grant all types of access to the abcsales1 account to all tables in the database, I can use the command shown below:

MariaDB [(none)]> GRANT ALL on Abc.* to 'abcsales1'@'localhost';
Query OK, 0 rows affected (0.04 sec)

MariaDB [(none)]> select user from mysql.db where db='Abc';
+-----------+
| user      |
+-----------+
| abcsales1 |
+-----------+
1 row in set (0.00 sec)

MariaDB [(none)]>

Bear in mind that the select user from mysql.db where db='dbname'; will only show users who have database-level access to the database named dbname. It is possible that an account can have access at the table, column, or proc level rather being granted access at the database level. To check for those access levels of access as well as database-level acess, use all of the commands below substituting the name of the database for dbname:

SELECT user,host FROM mysql.db WHERE db='dbname';
SELECT user,host FROM mysql.tables_priv WHERE db='dbname';
SELECT user,host FROM mysql.columns_priv WHERE db='dbname';
SELECT user,host FROM mysql.procs_priv WHERE db='dbname';

E.g.:

MariaDB [(none)]> SELECT user,host FROM mysql.db WHERE db='Abc';
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| abcsales1 | localhost |
+-----------+-----------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT user,host FROM mysql.tables_priv WHERE db='Abc';
Empty set (0.00 sec)

MariaDB [(none)]> SELECT user,host FROM mysql.columns_priv WHERE db='Abc';
Empty set (0.00 sec)

MariaDB [(none)]> SELECT user,host FROM mysql.procs_priv WHERE db='Abc';
Empty set (0.00 sec)

MariaDB [(none)]>

References:

1. how to know all users that can access certain database (mysql)?
   Asked: April 28, 2011
   Server Fault
2. Managing database access with MariaDB or MySQL
   Date: July 17, 2015
   MoonPoint Support

[/software/database/mysql] permanent link

MariaDB [accounts]> UPDATE Invoices SET Date_Sent='2015-08-29' WHERE Name='Acme';

If you don't the contents of the date field will appear as 0000-00-00.

You also need to enclose the date in quotes when querying a database. E.g.:

MariaDB [accounts]> SELECT * FROM Invoices WHERE Date_Sent='2015-08-29';

Otherwise, you will get an empty set for the query results.

[/software/database/mysql] permanent link

MariaDB [(none)]> use personnel;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [personnel]> UPDATE Sales SET `Work Phone` = '555.555.5555' WHERE LName = 'Smith'; 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [personnel]>

You can also use the command below to update the entry without first selecting the database with the Use command.

UPDATE `personnel.`Sales` SET `Work Phone` = '555.555.5555' WHERE `Sales`.`LName` = 'Smith';

References:

1. MySQL UPDATE QUERY
   tutorialspoint - Simply easy learning

[/software/database/mysql] permanent link

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ann                |
| crystal            |
| grover             |
| horticulture       |
| justiceleague      |
| maker              |
| mars               |
| moon               |
| orwell             |
+--------------------+
10 rows in set (0.09 sec)

MariaDB [(none)]>

You can see the tables within a particular database by selecing the database with use dbname, where dbname is the name of the database, and then using show tables;.

MariaDB [(none)]> use crystal
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [crystal]> show tables;
+---------------------------+
| Tables_in_crystaltokyo    |
+---------------------------+
| smf_admin_info_files      |
| smf_approval_queue        |
| smf_attachments           |
| smf_ban_groups            |
| smf_ban_items             |
| smf_board_permissions     |
| smf_boards                |
| smf_buddies               |
| smf_calendar              |
| smf_calendar_holidays     |
| smf_categories            |
| smf_collapsed_categories  |
| smf_custom_fields         |
| smf_gallery_cat           |
| smf_gallery_comment       |
| smf_gallery_pic           |
| smf_gallery_report        |
| smf_group_moderators      |
| smf_hcb_reminders         |
| smf_links                 |
| smf_links_bans            |
| smf_links_categories      |
| smf_links_comments        |
| smf_log_actions           |
| smf_log_activity          |
| smf_log_banned            |
| smf_log_boards            |
| smf_log_comments          |
| smf_log_digest            |
| smf_log_errors            |
| smf_log_floodcontrol      |
| smf_log_group_requests    |
| smf_log_httpBL            |
| smf_log_karma             |
| smf_log_mark_read         |
| smf_log_member_notices    |
| smf_log_notify            |
| smf_log_online            |
| smf_log_packages          |
| smf_log_polls             |
| smf_log_reported          |
| smf_log_reported_comments |
| smf_log_scheduled_tasks   |
| smf_log_search_messages   |
| smf_log_search_results    |
| smf_log_search_subjects   |
| smf_log_search_topics     |
| smf_log_spider_hits       |
| smf_log_spider_stats      |
| smf_log_subscribed        |
| smf_log_topics            |
| smf_mail_queue            |
| smf_membergroups          |
| smf_members               |
| smf_message_icons         |
| smf_messages              |
| smf_moderators            |
| smf_openid_assoc          |
| smf_package_servers       |
| smf_permission_profiles   |
| smf_permissions           |
| smf_personal_messages     |
| smf_picture_comments      |
| smf_pm_recipients         |
| smf_pm_rules              |
| smf_poll_choices          |
| smf_polls                 |
| smf_profile_albums        |
| smf_profile_comments      |
| smf_profile_pictures      |
| smf_scheduled_tasks       |
| smf_sessions              |
| smf_settings              |
| smf_smileys               |
| smf_spiders               |
| smf_subscriptions         |
| smf_tags                  |
| smf_tags_log              |
| smf_themes                |
| smf_topics                |
+---------------------------+
80 rows in set (0.00 sec)

MariaDB [crystal]>

You can view details on the columns in a particular table by using DESCRIBE tablename where tablename is the name of the table.

MariaDB [crystal]> DESCRIBE smf_smileys;
+--------------+----------------------+------+-----+---------+----------------+
| Field        | Type                 | Null | Key | Default | Extra          |
+--------------+----------------------+------+-----+---------+----------------+
| id_smiley    | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| code         | varchar(30)          | NO   |     |         |                |
| filename     | varchar(48)          | NO   |     |         |                |
| description  | varchar(80)          | NO   |     |         |                |
| smiley_row   | tinyint(4) unsigned  | NO   |     | 0       |                |
| smiley_order | smallint(5) unsigned | NO   |     | 0       |                |
| hidden       | tinyint(4) unsigned  | NO   |     | 0       |                |
+--------------+----------------------+------+-----+---------+----------------+
7 rows in set (0.05 sec)

MariaDB [crystaltokyo]>

You can also use EXPLAIN tablename, since EXPLAIN and DESCRIBE are synonyms, but the DESCRIBE keyword is more often used to view information about a table's structure, whereas EXPLAIN is more often used to obtain a query execution plan, i.e., an explanation of how MySQL would execute a query.

References:

1. Showing all MySQL databases or all tables in a database
   Date: July 27, 2014
   MoonPoint Support
2. MySQL :: MySQL 5.0 Reference Manual :: 13.8.2 EXPLAIN Syntax
   MySQL :: Developer Zone

[/software/database/mysql] permanent link

[ More Info ]

[/software/database/mysql] permanent link

I verified that the php-mysql package was installed with rpm -qi php-mysql.

# rpm -qi php-mysql
Name        : php-mysql
Version     : 5.4.16
Release     : 23.el7_0.3
Architecture: x86_64
Install Date: Tue 11 Nov 2014 08:26:15 PM EST
Group       : Development/Languages
Size        : 237259
License     : PHP
Signature   : RSA/SHA256, Fri 31 Oct 2014 10:24:56 AM EDT, Key ID 24c6a8a7f4a80eb5
Source RPM  : php-5.4.16-23.el7_0.3.src.rpm
Build Date  : Fri 31 Oct 2014 09:07:27 AM EDT
Build Host  : worker1.bsys.centos.org
Relocations : (not relocatable)
Packager    : CentOS BuildSystem <http://bugs.centos.org>
Vendor      : CentOS
URL         : http://www.php.net/
Summary     : A module for PHP applications that use MySQL databases
Description :
The php-mysql package contains a dynamic shared object that will add
MySQL database support to PHP. MySQL is an object-relational database
management system. PHP is an HTML-embeddable scripting language. If
you need MySQL support for PHP applications, you will need to install
this package and the php package.

I created a PHP test page with the following code:

<html>
<head>
<title>PHP Test</title>
</head>

<body>

<h2>A test page</h2>

<?php echo "<p>Hello world</p>"; ?>

<?php phpinfo(); ?>

</body>
</html>

"Hello world" was displayed by the PHP echo command and the information from the phpinfo function was also displayed. I searched through the results displayed for references to "MySQL" and found mysql and mysqli sections, including the following:

mysql

Since it appeared that PHP support for MySQL was present, I restarted Apache with apachectl restart, though I didn't expect that to resolve the problem. But when I refreshed the web page afterwards, the information from the MySQL database was displayed. Apparently, I should have restarted Apache after I ran the systemctl start mariadb.service to start the MariaDB database service yesterday. MariaDB is a fork of MySQL.

References:

1. Fatal error: Call to undefined function mysql_connect()
   Date: May 16, 2012
   stackoverflow
2. Resolving a Fatal error: Call to undefined function mysql_connect() in RedHat
   By: Shailesh N. Humbad
   Created: October 18, 2004
   Last Modified: July 24, 2011
   Somacon
   Articles on web development, software, and hardware

[/software/database/mysql] permanent link