bookmark_borderSQL: Delete all Rows with “foo” in the String

Let’s say you’re building something cool and you try to share this amazing thing with a friend. Then that friend decides to blow your DB up with useless entries. Because that is what friends do.

In such a scenario, you might end up with something like this:

select count(*) from table;
| count(*) |
|    12687 |

If this is you, you might find yourself a bit distraught. But that’s ok, life is not that bad. Hopefully your friend did something like this:

|   12674 | foofoo12664      |        | foofoo12664     | $2y$10$102qeaPYWOzdIrXX2xNTIeOfEjHDKPW9xSRpByRTXas1MgkF7SOby | NULL    |
|   12675 | foofoo12665      |        | foofoo12665     | $2y$10$rcwXM0qSM20OrY8RQIQtU.ztcy5B2kVlT1C09R1ugJudxDBWw.7BK | NULL    |
|   12676 | foofoo12666      |        | foofoo12666     | $2y$10$kCzO15VoVs.l5F2112WY8eAyByLz9Bm1AC/bklQRE3pddWEEjWqHm | NULL    |
|   12677 | foofoo12667      |        | foofoo12667     | $2y$10$lVZVuNIhAwVPTmuJpyyUoO1..HQJhcFmqiGYVBdMCYlLIip4m8xr. | NULL    |
|   12678 | foofoo12668      |        | foofoo12668     | $2y$10$X8RplBFJpeShCg0qnsy64ecloXAtRfotgymi2WR709bTZ6.uN4kem | NULL    |
|   12679 | foofoo12669      |        | foofoo12669     | $2y$10$LVCjD9fqOVXi6A2LVX5FMOSTWnwq5WUK959Kw/M1QeTUsO3qaTBcm | NULL    |
|   12680 | foofoo12670      |        | foofoo12670     | $2y$10$smcLn6eirBkKeZayWVP4mONcoRfEWJOwYRbK08LndCO4F2hJ2Xj.G | NULL    |
|   12681 | foofoo12671      |        | foofoo12671     | $2y$10$mY11LeuTrz4sPLRov2nJceY974TDUIzqzO5spzTkKUDJowMpyszkW | NULL    |
|   12682 | foofoo12672      |        | foofoo12672     | $2y$10$fZJEkthgTXsWHRVYBsQMHuqJFn6FFIHg1vqibm8hqVH4f0yAbuHk2 | NULL    |
|   12683 | foofoo12673      |        | foofoo12673     | $2y$10$1/swwPhmHy66Oole.6Fepeh4hxJDwlOroMlDtCkgggxnsQsjAC86. | NULL    |
|   12684 | foofoo12674      |        | foofoo12674     | $2y$10$Whrwt8j19R4jFC6KdjGLoupsG78mH2fTzXIR78.lfQ/6xtZ/mNc3u | NULL    |
|   12685 | foofoo12675      |        | foofoo12675     | $2y$10$U0o9q3xWAOD2q/L2MBWn/OH4M5mJPODuk07fnfGIbTeKgaLDbBnka | NULL 

In this scenario you can see common each row has similarities (i.e., “foofoo”). Because of this you can open your terminal and use the “Delete” command like this:

DELETE FROM table WHERE usersUid LIKE 'foofoo%';

This will remove each record in the table. This is what you need to know of the command above:

tablethe table storing the data;
usersUidthe column you are going to query;
%his operator is the wildcard, in my scenario I wanted it to delete every row that started with “foofoo” because that was the syntax they used. If I used the % operator before the query words, it would look for that syntax anywhere in the string.

You can then buy your friend a bag of coal for the holidays.


bookmark_borderCreate MySql Database Backup in Linux Using MariaDB

If you need to do a MySQL database backup in linux, this is the basic command structure you want to use:

# mysqldump -u [username] -p [database name] > [filename]-$(date +%F).sql

This will prompt the password when you hit enter.

Continue reading “Create MySql Database Backup in Linux Using MariaDB”

bookmark_borderMySQL – ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

If you run into this error it’s a strong indicator that the Password Validation Plugin is installed.

The quick and dirty way to fix this is to uninstall the plugin. You will need to be the root user in the database.

Continue reading “MySQL – ERROR 1819 (HY000): Your password does not satisfy the current policy requirements”

bookmark_borderSetting Root Password on MySql when it’s Empty

I was working on one of my servers when I realized that I had done a bone-head mistake. I left the root password blank in MySQl. 

It was not as easy as I thought to fix the problem. There were a couple of things I didn’t account for, specifically that when you’re initially setting up MySQL on Ubuntu and don’t provide a password to the root user, it will use the auth_socket plugin. That plugin doesn’t care and doesn’t need a password. It just checks if the user is connecting using a UNIX socket and then compares the username.

Continue reading “Setting Root Password on MySql when it’s Empty”

bookmark_borderHow do you show and update a MySQL database in a linux terminal?

This post will walk you through the process of showing and updating a MySQL database via terminal.

We’ll use a WordPress installation because it has an established database schema. The principles apply to any database.

Continue reading “How do you show and update a MySQL database in a linux terminal?”