-1

I have a Magento database in which I want to search for a particular string/pattern.

But the database's size is too large so I cannot export the database to .sql file and then search into that file(editor even Geany crashes opening such large files).

So how can I do a search the database for a perfect match of [string/pattern] and display fulltext information as result, through only using command-line and MySQL Database credentials ?

I tried below command, but it requires username to be given as -u[USERNAME], also it doesn't display full query or result in terminal window.

mysqldump -p[PASSWORD] [DATABASE] --extended=FALSE | grep [pattern] | less -S

Anyone have any solutions for this ?

Shadow
  • 33,525
  • 10
  • 51
  • 64
Vicky Dev
  • 1,893
  • 2
  • 27
  • 62
  • If you have the data in a database, then why don't you use sql to search the data? Mysql does have a command line client. – Shadow Oct 28 '16 at 05:36
  • mysql -u [USERNAME] -p [password] -e 'SELECT * FROM blabla WHERE field = pattern' – Zamrony P. Juhara Oct 28 '16 at 05:37
  • There are a hell lot of tables in a Magento database (atleast over 135), so I don't think that's a faster option, that's why I asked for searching in `database` rather than `column` or `table`. Thanks for effor though. – Vicky Dev Oct 28 '16 at 05:47
  • It is clearly explained what I need in the question, and in some situations this is what is only thing needed, so the downvoter must be unaware and non-experienced of those situations. – Vicky Dev Oct 28 '16 at 06:07
  • @VickyDev - You didn't clearly explain. When you say "that's why I asked for searching in database rather than column or table" I can't see anywhere in the question where you asked that. – Enigmativity Oct 28 '16 at 06:20

3 Answers3

0

You can first log into MySQL CLI as especified in http://dev.mysql.com/doc/refman/5.7/en/connecting.html

mysql --host=localhost --user=myname --password=mypass mydb

So, you can use a query command to find your pattern. If you know the table you want to search such as the column it make the thinks easy. The SELECT statement is like this:

SELECT column FROM table WHERE column LIKE '%pattern%';

http://dev.mysql.com/doc/en/select.html

If you don't know the table's name, you can list all and try to find by the meaning.

SHOW TABLES;
Joo felipe Chiarelli Bourschei
  • 24
  • 4
  • There are a hell lot of tables in a Magento database (atleast over 135), so I don't think that's a faster option, that's why I asked for searching in `database` rather than `column` or `table`. Thanks for effor though. – Vicky Dev Oct 28 '16 at 05:47
  • http://stackoverflow.com/questions/639531/search-in-all-fields-from-every-table-of-a-mysql-database I think that there is no easy way to peform what you want. Could you give more information what you looking for? – Joo felipe Chiarelli Bourschei Oct 28 '16 at 05:50
0

Edited with better code

You didn't say if this was a one off or not but this will check all tables in a schema for a value.

First in your home directory set up a file named .my.cnf with the following contents and change its permissions to 700 (Replace [USERNAME] and [PASSWORD] with your username and password.

[client]
user=[USERNAME]
password="[PASSWORD]"

Then execute the following (Replacing [DATABASE] and [CHECKSTRING] with your database and the check string)

mysql [DATABASE] --silent -N -e "show tables;"|while read table; do mysql [DATABASE] --silent -N -e "select * from ${table};"|while read line;do if [[ "${line}" == *"[CHECKSTRING]"* ]]; then echo "${table}***${line}";fi;done;done

If checking for 51584 the result would be something like

test_table***551584,'column 2 value','column 3 value'
test_table5***'column 1 value',251584,'column 3 value'

If you want to know which column had the value then select from INFORMATION_SCHEMA.COLUMNS and add another nest.

mysql [DATABASE] --silent -N -e "show tables;"|while read table; do mysql [DATABASE] --silent -N -e "select column_name from information_schema.columns where table_schema='[DATABASE]' and table_name = '${table}';"|while read column; do mysql [DATABASE] --silent -N -e "select ${column} from ${table};"|while read line;do if [[ "${line}" == *"[CHECKSTRING]"* ]]; then echo "${table}***${column}***${line}";fi;done;done;done

If checking for 51584 the result would be something like

test_table***column1***551584
test_table5***column2***251584
Kep Brown
  • 1
  • 1
  • Whe I run your command I get message `Access denied for user 'user'@'localhost' (using password: NO)`. Even though I provided correct username and password. – Vicky Dev Oct 28 '16 at 07:18
  • Updated the response to include creating a .my.cnf file. This file allows you to run mysql command line commands without having to enter in a username and password. Remove it once you are done if this is on a production machine. – Kep Brown Oct 28 '16 at 08:08
  • `@KepBrown` It' a live site, with cpanel and `/home/user/public_html` folder. Where should I put this my.cnf file, will permissions 700 apply ? – Vicky Dev Oct 28 '16 at 10:25
  • In that case replace – Kep Brown Oct 28 '16 at 21:41
  • replace all instances of mysql [DATABASE] with mysql --user=[USERNAME] --password=[PASSWORD] [DATABASE] – Kep Brown Oct 28 '16 at 21:44
0

First of all you need to login into database with correct username and password by below command.

sudo mysql -u root -p

then check the database in which you want to operate operation. eg.

SHOW DATABASES;

USE Test;

now your database is ready for operation through terminal. Here I assume my database name is "Test".

Now for String/pattern matching use command as below or follow the link http://www.mysqltutorial.org/mysql-regular-expression-regexp.aspx.

SELECT column_list FROM table_name WHERE string_column REGEXP pattern;

Anoop Kumar
  • 845
  • 1
  • 8
  • 19