@WLS-ITGuy If you wanted to say that old domain is part of usernames, and you want to change only that part of the usernames, there is no single SQL statement, but I see 2 solutions:

Write SQL script that will use LOOP to iterate through all records in table users and then replace username with new username....

Use spreadsheet calculator (Excel or similar) to prepare multiple simple SQL UPDATE statements:

Export table "users" (just necessary columns) and import it to Excel or similar _ - prepare new usernames in new column (use SUBSTITUTE() function in Excel) prepare simple UPDATE command for each username (in new column in Excel) - you can use functions like SUBSTITUTE or CONCATENTE to prepare you UPDATE statements with your usernames from previous column, i.e. =CONCATENATE("UPDATE users SET username = '", .... copy UPDATE statements from Excel and execute them

I think secong approach (Excel) is much safer because you see results in spreadsheet before you execute UPDATEs.
Always use WHERE clause when you use UPDATE

If you want more exact statements/formulas, give more detailed structure of your table and usernames

I've seen that a lot. When I import Drupal databases, I learnt to use its Backup and Migrate module instead, it works flawlessly. Downside is you need to do clean Drupal installation first, but that basically is just creating blank database and putting brick on enter key.

@scottalanmiller said in Bookstack Backup to S3:

@wrx7m said in Bookstack Backup to S3:

@dbeato said in Bookstack Backup to S3:

@wrx7m said in Bookstack Backup to S3:

@dbeato Are you running bookstack in AWS?

Not at the moment. I am just backing up to S3.

Look at Wasabi for S3 compatible object storage. Significantly cheaper.

Yup, can't think of any reason to use S3 unless you are hosted on AWS and aren't using it for backup but for production storage. And even then, not the majority of the time. S3 is too expensive and doesn't really offer anything except locality that is of any specific value. B2 and Wasabi beat it a lot.

Sorry, I posted this as S3 but I should change it to Wasabi. It is the same Idea for both.

@DustinB3403 said in MySQL MariaDB password reset without knowing the password:

Just still seems weird to have the password in plaintext on in a config file.

has to be somewhere, how else can an application connect? Look at WordPress, for example, you have to enter the password into the application so that the application can connect to the database. Otherwise, the database would have to have no password protection at all or you'd need a human to log in and enter the password every time a database connection was needed (which is normally thousands of times a minute.)

I've done both. I tend to like MariaDB / MySQL better because that is what I'm more comfy with.

@black3dynamite said in Setting Up a Standard MySQL or MariaDB Database for an Application:

@JaredBusch said in Setting Up a Standard MySQL or MariaDB Database for an Application:

I like my approach to setting this up.

Obviously, install MySQL/MariaDB first as noted above.

Then do the following. This all needs done in the same SSH session, but otherwise things are simple.

Choose once of these exports for your DB root password.

The first one is for you to specify, the second generates a random one and echo's it back to you.

# Specify your own password for MariaDB root user export DB_ROOT_PASS="somebigpasswordgoeshere" # Generate a random password for MariaDB root user export DB_ROOT_PASS="$(head /dev/urandom | tr -dc A-Za-z0-9 | head -c 30)" echo "This is your MariaDB root password: $DB_ROOT_PASS" Specify the application database name and application user name # Database user to use for application export DB_USER='yourusername' # Database name to use for application export DB_NAME='yourdatabasename' Generate or specify a random password for the database user # Specify your own password for the application's database user export DB_PASS="somebigpasswordgoeshere" # Generate a random password for the application's database user export DB_PASS="$(head /dev/urandom | tr -dc A-Za-z0-9 | head -c 30)" echo "This is your password for the application user: $DB_PASS" Then create the application database, use, and grant access. mysql -e "CREATE DATABASE $DB_NAME;" mysql -e "CREATE USER '$DB_USER'@'localhost' IDENTIFIED BY '$DB_PASS';" mysql -e "GRANT ALL ON $DB_NAME.* TO '$DB_USER'@'localhost';" mysql -e "FLUSH PRIVILEGES;" Finally, lock down the system without the interactive requirement of mysql_secure_installation # Secure MariaDB (this does what mysql_secure_installation performs without interaction) mysql -e "UPDATE mysql.user SET Password=PASSWORD('$DB_ROOT_PASS') WHERE User='root';" mysql -e "DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');" mysql -e "DELETE FROM mysql.user WHERE User='';" # Beginning on some version of MariaDB after Fedora 29 was released, the test DB is no longer there by defualt. mysql -e "DROP DATABASE test;" mysql -e "FLUSH PRIVILEGES;"

Your approach makes it easier to use as part of a script.

It also generates random passwords, which I prefer.

@Dashrender said in Make an osTicket Database Backup:

Total DB Admin noob here - so bare with me.

Does this include the username/password setup for this DB? or does that need to be maintained separately?

i.e. you have to rebuild from this backup - do you just create a brand new SQL user and grant them rights to this DB upon import, then use that new account to give access to the app?

In typing out my question I kinda assume the answer is the second bit - you just create a new user and assign them rights.

Correct. The user and the database are separate things.

@magicmarker

Why dont they create one giant hazardous label to fit all the products, and make it generic as possible.

Like this product contains hazardous chemicals.

it is not like someone actual reads that small fine print.

Another problem solved...

@JaredBusch said in Zabbix Server Setup on Debian 9.0:

@dbeato said in Zabbix Server Setup on Debian 9.0:

Prior to doing anytighing if you are basing this on a Debian 9 NetInstall you need to install the following packages as root

apt install ufw sudo -y

Then setup the user in the sudo group

sudo adduser username sudo

Technically, you do this if you don't select certain packages during the installation. It has nothing to do with it being a NetInstall ISO or not.

Technically, these packages are selected by default. I believe both sudo and ufw are part of standard system utilities.
8CO2bH3.png

Additionally ufw is not the firewall. That is iptables, but ufw is the simple way to activate and use iptables.

Yes, netinstall has that option as below Figure 1 while though it does not install ufw and sudo on those standard utilities Figure 2 & 3 with the fresh install Figure 4

Figure 1
0_1543334144965_e22c5437-43f1-474c-968f-599d675bb766-image.png
Figure 2
0_1543337768264_aa49f222-93d1-437a-89a6-3dbfc558f1df-image.png
Figure 3
0_1543337818820_cb5a9e3c-8a1b-4022-a373-9f1f41bcba52-image.png
Figure 4
0_1543338000904_b04d0f07-af0f-4ada-9958-8b3dfdd3027d-image.png

@bbigford said in osTicket: Basic Ticket Report of All Tickets with Subject:

Since there is no inner or outter join specified, inner join is obviously being used where 'join' is specified (outter join having to be explicitly defined where an outter join is needed). How come you explicitly specify 'inner join' at the bottom when you could just use 'join' again as your inner join?

Obviously join and inner join accomplish the same thing, just curious why one is not explicitly defined and the other is.

I expanded on something done by someone from the osTicket team. it was some weird stylistic choice that they made and I just followed.

@jaredbusch said in MariaDB on Fedora 27 Fails to Start from RocksDB Inclusion:

@scottalanmiller So as my systems update over the next 24 hours they are all going to break?

Hit us on update, no restart involved 😞

@fuznutz04 said in MySQL configuration best practices:

@emad-r

What is your practice for log rotation? For example, the error log. If that grows (obviously indicating other issues) , and needs to be rotated, what procedure do you use to rotate logs?

Currently the third party vendor uses this:

expire_logs_days = 10
max_binlog_size = 100M

In our MySQL instance.

@jaredbusch said in MariaDB - Remote connections from specific IP:

@fuznutz04 said in MariaDB - Remote connections from specific IP:

@scottalanmiller Well that worked, but it looks like I can connect from any IP, not just the one I specified. Digging further.....

You don't want that first rule. That opened it to everything. You only want the second.

Bingo. Thanks!

Transcript:

Today what I want to talk to you about is a standard pattern for developing bespoke or in-house custom software for
the small business market. in SMBs it's relatively common that someone wants to make a small application, and
traditionally we used things like MS Access and some stuff like that to do this. And that's been less than ideal, and I think
most people understand that they don't want to do that, but a lot of people don't know where to go other than "not
Access" and that leaves some problems as you can kind of skew off in all kinds of directions as you try to figure out what
you should do rather than focusing on what you shouldn't. One of the problems here is that there are so many
ways you can make this work, but there's only so many that really make sense in the majority of circumstances and that's
why we kind of have a standard standard pattern.

It's not a best practice, it's nothing like that, but there is a pattern of how to approach this as kind of a baseline of "unless you have a reason not to do things this way consider that this is probably the way to do it", and what that is is want so a couple things: one - your application is going to be web-based unless you have a really strong technical reason why it can't be and you'd better be sure about that. It's going to be web-based - that's what a modern application looks like and it's
what a modern application has looked like for a long time. Do not assume that you're going to start with something
else and only go web-based if you have to; it's you assume you go web-based you're going to have a web interface and
there are situations where that doesn't apply, but don't start somewhere else start with the baseline move away from
it where it's applicable to do this.

The language of choice if you are not already a competent programmer in some specific language is PHP; not because PHP is the most amazing language ever, not because PHP provides us all kinds of technology we don't have other places,
but because it is simple, it is universal, it is very functional, it is custom designed for this exact use case, and it is super easy to find people with experience in PHP to help you whether it's online or you've got to hire someone or your company needs to maintain this in the future. For all of those purposes PHP is the ideal language, not only that but if you need to hire PHP they tend to be lower in cost than a lot of other languages.

You are neither constrained because it is a language that is very hard to learn or very obscure like f-sharp or Ocaml or
something like that, nor are you charged a premium because you're dealing with a language that is archaic and the only
people who would be using it or people who are trapped with it say COBOL or Fortran or Visual Basic. So PHP really
fits into a perfect spot here where it meets all the checkboxes that you would reasonably have for a normal business
application.

If you need something other than PHP chances are you will know and it won't actually be that you need something else it's that something else will have a specific benefit that makes sense. For example Ruby on Rails - you may already know Ruby or you may be interfacing with other Ruby components - so Ruby on Rails might make great sense or you may be a Python developer already and Python with Django just fits your needs. All that's fine, but if you don't have something driving you to some other language assume PHP.

One of the really big reasons for this as opposed to say C# or VB.NET that a lot of people feel might be easier to use one PHP is easier to it's more universal in three it doesn't provide any lock in whether real or perceived to an expensive
proprietary platform. PHP allows you to deploy whether to a platform as a service provider or to your own systems
whether there are free ones or paid ones or whatever. It provides you the flexibility in the future to do what's needed - even if you have things today that lock you in that doesn't mean you should invest into that technical debt.

If you're making a new investment in new code it should be towards something that gives you the freedom to do what you
need in the future this is just basic protections basic best practices around developing don't create lock-in without very clear reason to justify it and sometimes even with very clear reason really really work hard to justify that locking is a terrible thing. So we also assume with the rarest of exceptions that your application is going to need a database to hold the data on the back end. Not always true, but certainly most times if you don't need one just don't use one. If you do need one look to MongoDB or MySQL as a lot of people have used that in the past, MariaDB is kind of where
the development is going in the future. A lot of the the best platforms or the most common platforms have moved to
MariaDB from MySQL for those who are not aware MariaDB is a drop-in replacement for MySQL. Don't don't consider those two different products. Just if you're if you're getting one go check out MariaDB to get that it that's really ideal if
you need a relational database that's on the the slightly leaner side. It is fast and easy and lots of people know it, but
it's it's not the most robust it's pretty robust it's a good platform but if you need a really powerful robust database or you're really versed in this one PostgreSQL is the other really awesome choice.

Both of these are all three of these really are completely free you have unlimited open source options. You can deploy to Windows, you can deploy to Linux, you can float deploy to BSD, you can deploy them Solaris, you can put it on Mac, you have no lock-in. You can put it wherever makes sense including unlimited free platforms and hosting without limitations. So you are free and clear to do what you need to do. That said it's also important to consider that relational databases may not make sense to you for you for your project. A lot of things don't people jump it to that a lot of times when they're making internal software and PHP specifically is super easy to work with MySQL or MariaDB. Most documentation kind of
assumes that that's what you're going to do there are loads of NoSQL options out there such as Redis or MongoDB or
Cassandra, all kinds of things, and they may make great choices for you, depending on the type of data that you're going to
be storing.

If you're making a really simple application you're probably going to lean towards traditional relational databases not because they make sense, but because they're easy and assumed but if you're doing a lot of things, especially if you're doing distributed software where you may need to handle like failover or unbelievable performance or geographically disparate locations, a lot of NoSQL options may be very very appropriate for you. And remember it is actually high complexity that tends to push you towards relational databases and simplicity that pushes you away - that's not a given thing, but like for example Redis is a key value pair database so it tends to be very good for extremely basic use case
whereas something like MongoDB is a document database and it's very very good for document style data great for
light posts on a website or tracking assets things like that it tends to be very very strong.

Whereas relational data but relational databases tend to be very good for financial data where you need really really strong relations between lots of different actors and objects. So there's a lot to choose from there, but make sure when you're looking at your database and your platforms that you're looking at things that are not going to lock you in unnecessarily for the future, because that will cost you potentially an extreme amount of money because what is minor technical debt today can be crippling technical debt just a year or two down the road. And when your successors come they may be very unhappy to find that you are in a situation where you've created an amount of debt that will cost them more than recreating the project to get away from.

So that's just a baseline to start with and I think really gives a strong almost every time we have a conversation which is
pretty often about how people are going to approach a problem like this the answer really comes out to being PHP and
bring a DB it's free, it's easy, there's so many resources for it, there's always someone to help you, it's a good place to
start only vary from that if you find your reasons you have to.

@WLS-ITGuy said in MySQL to PostgreSQL conversion:

Well, that was a thread that will fizzle out quickly 🙂

Thanks for the assist all.

LOL, sometimes that's exactly what you want. This would have been a large, horrible project. Now it's a simple "Oh good, we are all set" 🙂

@DustinB3403 said in MariaDB Backup and Move:

@scottalanmiller cool, so if I am already collecting the rest of the database (see previous topic from today using rsync) then backing up the mariadb should grab what is left.

Then all that would be left is to push the backup to an secondary system and test that all works.

In theory. I've had unreliable recoveries from that. I don't know any method to get 100% backup if that code is stored in the database.