MySQL

PHPMyAdmin: Securing web console

Posted on

I installed PHPMyadmin to check my help manage my database, eventhough I also use the desktop program MySQL Workbench, I fell sometimes its important to use the webconsole. Recently, upon checking my MySQL Logs (/var/logs/mysql/error.log) I encountered that everyday I had thousands of failed logins to the PHPMyAdmin. I sure enough have a very strong […]

MySQL

MySQL: Enable Logging

Posted on

mysql> show variables like ‘%log%’; Recreate the log files I deleted by mistake. It appears the MySQL does not recreate them automatically. touch /var/log/mysql.log chown mysql:adm /var/log/mysql.log touch /var/log/mysql/error.log chown mysql:adm /var/log/mysql/error.log touch /var/log/mysql/mysql-slow.log chown mysql:adm /var/log/mysql/mysql-slow.log touch /var/log/mysql/mysql.log chown mysql:adm /var/log/mysql/mysql.log

MySQL

CSV IMPORT MANUAL

Posted on

Remove Commas from strings Truncate String Length using LEFT function in Excel Use CLEAN Function to remove unwanted breaks and non displaying characters

MySQL

MySQL: Assign Opportunities to User based on Parent Account

Posted on

Query to Select List:   SELECT * FROM opportunities LEFT JOIN accounts_opportunities ON accounts_opportunities.opportunity_id = opportunities.id LEFT JOIN accounts ON accounts_opportunities.account_id = accounts.id WHERE accounts.assigned_user_id = ‘7077c616-4f2c-f966-d9b6-5581807bae86’;     UPDATE Query: UPDATE opportunities x LEFT JOIN accounts_opportunities y ON y.opportunity_id = x.id LEFT JOIN accounts z ON y.account_id = z.id SET x.assigned_user_id = ‘7077c616-4f2c-f966-d9b6-5581807bae86’ WHERE […]

MySQL

MySQL: Assigned Calls to User based on Parent Account or Lead

Posted on

Select List of Calls to Update: SELECT * FROM calls LEFT JOIN accounts ON calls.parent_id = accounts.id LEFT JOIN leads ON calls.parent_id = leads.id WHERE ( accounts.assigned_user_id = ‘7077c616-4f2c-f966-d9b6-5581807bae86’ OR leads.assigned_user_id = ‘7077c616-4f2c-f966-d9b6-5581807bae86’ ) ;   Update Query: UPDATE calls x LEFT JOIN accounts y ON x.parent_id = y.id LEFT JOIN leads z ON x.parent_id […]

MySQL

MySQL: UPDATE or DELETE with JOIN Operators (Contacts)

Posted on

Same as previous post but this time with Accounts instead of Leads. List the Contacts related to the Accounts this employee has assigned: SELECT contacts.* FROM accounts INNER JOIN accounts_contacts ON accounts.id = accounts_contacts.account_id INNER JOIN contacts ON accounts_contacts.contact_id = contacts.id WHERE accounts.assigned_user_id = ‘7077c616-4f2c-f966-d9b6-5581807bae86’ AND contacts.deleted = 1 ; *** Now that I have done […]

MySQL

MySQL: UPDATE or DELETE with JOIN Operators

Posted on

I’m trying to update some records on Table C (contacts.assigned_used), but I have a Join from Table A (leads) and passing through in intermediary Table C. The query to show the filtered Table C (which is what I want to update) is: SELECT contacts.* FROM leads INNER JOIN leads_contacts_c ON leads.id = leads_contactsleads_ida INNER JOIN […]

MySQL

MySQL: External Connection to Database Blocked

Posted on

Cannot establish an external MySQL connection to the server. Error 1.  Error 10061: connect to MySQL server failed Fixed it using the following solution (https://help.ubuntu.com/community/ApacheMySQLPHP) Solution 1. Set mysql bind address Before you can access the database from other computers in your network, you have to change its bind address. Note that this can be a security problem, […]