MoonPoint Support Logo

 

Shop Amazon Warehouse Deals - Deep Discounts on Open-box and Used ProductsAmazon Warehouse Deals



Advanced Search
December
Sun Mon Tue Wed Thu Fri Sat
9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31        
2024
Months
Dec


Sat, Sep 09, 2017 11:53 pm

Backing up a MySQL or MariaDB table or database

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

Mon, Jan 16, 2017 10:35 pm

Selecting records containing a string in a MySQL or MariaDB database

If you wish to select only the records containing a particular string, e.g., some word, etc., from a MySQL or MariaDB database table then you can use LIKE in the SQL SELECT command. E.g., if I have a table named Books that contains book information and one of the columns in the database is Title, I can select any books from the database where the title contains "Unix" with the command below:

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

Sat, Feb 13, 2016 10:30 pm

Updating MySQL or MariaDB table entries where a criterion is met

If you want to update all entries in a MySQL or MariaDB database table for entries that meet a specific criterion, you can use a Structured Query Language (SQL) command like the one below:

Update table_name
SET column_name = "new_value"
WHERE colum_name = "old_value";

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:

Update Students
SET LastName = "Lamb"
WHERE LastName = "Smith";

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.

Update Students
SET LastName = "Lamb", Address="1234 Cherry Lane"
WHERE LastName = "Smith";

[/software/database/mysql] permanent link

Sat, Jan 30, 2016 10:08 pm

Locating a MySQL password in the Bash history file

If you need to recover a forgotten MySQL or MariaDB password, if the password was entered at a shell prompt while starting either program with the mysql command and the user's account uses the Bash shell, you may be able to find the password in the Bash history file for the user's acount, which is .bash_history in the user's home directory. E.g., if the user entered the command below:
$ 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

Sun, Nov 22, 2015 8:43 pm

Viewing accounts that have access to a MySQL or MariaDB database

If you wish to know which accounts have access to a MySQL or MariaDB database, you can use the command select user from mysql.db where db='dbname'; where dbname is the name of the relevant database. E.g., if I want to view access to the Abc database, I can use the command below while logged into the MySQL/MariaDB DBMS as root (you can log in using the MySQL/MariaDB root account with mysql -u root -p):
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

Sat, Sep 05, 2015 10:48 pm

Updating a date entry from the command line in MySQL

When you update an entry for a column containing dates in a table in a MySQL or MariaDB database, you need to enclose the date in quotes. E.g.:

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

Wed, Sep 02, 2015 10:40 pm

Updating a MySQL table entry with a space in the table name

If you need to update the value for a column in a MySQL or MariaDB table, two relational database management systems named after the daughters, My and Maria, of the lead developer for the projects, Michael Widenius, where there is a space in the column name, use the backtick , aka, bakckquote, character, i.e., `, to enclose the name of the column. E.g., to update an entry in a table named "Sales" in a database named "packages", if the field was named "Work Phone", I could use the following:
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

Mon, Aug 31, 2015 10:28 pm

Viewing information about MySQL and MariaDB databases and tables

MySQL and MariaDB, which is a fork of MySQL are relational database management systems that share a common command syntax. For both, you can see available databases with the command show databases.
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

Fri, Jul 17, 2015 10:52 pm

Managing database access with MariaDB or MySQL

Database access for MariaDB or MySQL database users can be controlled through the GRANT, REVOKE and DROP commands. You can see a list of the users with the select user from mysql.user; command. If you add host to that command, e.g., SELECT user, host from mysql.user;, you can also see the hosts/IP addresses from which users are allowed to connect to databases.

[ More Info ]

[/software/database/mysql] permanent link

Wed, Nov 12, 2014 9:58 pm

Call to undefined function mysql_connect()

I finally resolved problems with access to MySQL databases on a CentOS 7 server from the command line yesterday - see MySQL service not running on CentOS 7 system - but when I visited web pages on the server that rely upon PHP code to access MySQL databases on the server, I was seeing blank pages, though other PHP web pages that didn't have any database access were displaying properly. When I checked the Apache error log for a website where the PHP code should have displayed data from a database, I saw "PHP Fatal error: Call to undefined function mysql_connect()" associated with attempts to load the web page containing the PHP code that connected to the MySQL database.

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

MySQL Supportenabled
Active Persistent Links 0
Active Links 0
Client API version 5.5.37-MariaDB
MYSQL_MODULE_TYPE external
MYSQL_SOCKET /var/lib/mysql/mysql.sock
MYSQL_INCLUDE -I/usr/include/mysql
MYSQL_LIBS -L/usr/lib64/mysql -lmysqlclient

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

Valid HTML 4.01 Transitional

Privacy Policy   Contact

Blosxom logo